Excel REPLACE Function (11 examples)

Excel REPLACE Function

The Excel REPLACE Function replaces part of a text string with a different text string, and the function works based on the position and number of characters of the text.

Examples

1. How to replace the first word in excel.
2. How to replace the second character of the first word.
3. How to replace the third word in excel.
4. How to replace hyphen of the phone number with space in excel.
5. How to replace text by variable position.
6. How to generate numbers after special characters.
7. How to replace the first two characters with space.
8. How to replace numeric values in excel.
9. How to replace dates in excel.
10. How to insert hyphen (-) between numbers.
11. How to replace multiple text with REPLACE. [Nested REPLACE]

---

The Excel function REPLACEB performs the same job based on the number of bytes users specify. This function is intended for use with languages (e.g., Chinese, Korean, Japanese) that use double-byte character set.

Syntax

REPLACE(old_text, start_num, num_chars, new_text)

where,

• Old_text : is a required text that users want to replace with new characters.
• Start_num : is the position of the character in old_text that users want to replace with new_text. This is a required argument.
• Num_chars : is the number of characters in old_text that users want to REPLACE with new_text. This is also a required argument.
• Num_bytes : this argument is used with REPLACEB. It is the number of bytes in old_text that users want to replace with new_text.
• New_text : This required argument is the text that will replace characters in old_text.

wikitekkee note:

1. Space is also counted as a character; therefore, users MUST count the space while calculating the text position.
2. The Excel REPLACE function can be user with FIND function.

Example 1: Replace the first word:

Question: We want to change the first word, “to” with a new word “To”.

Answer: The formula is =REPLACE(B4, 1, 2, “To”), which replace the word “to” with new word “To”.

Example 2: Replace the second character of the first word:

Question: How can I change the second character of the first word?

Answer: The formula is =REPLACE(B4, 2, 1, “o”), which replaced the word “TO” to “To” (changing the upper case of the letter “O” to lower case “o”.)

Example 3: Replace the third word.

Question: How can I change the third word in my text?

Answer: The formula is =REPLACE(B5, 9, 4, “extra”), which replaced the third word “More” with “Extra“.

Example 4: Replace hyphen (-) with space:

Question: How can I replace the hyphen (-) with space in excel?

Answer: The formula is =REPLACE(REPLACE(B5,4, 1, ” “), 8, 1, ” “), which replaced the hyphens (-) with space.

Example 5: Replace text by variable position.

Question: How can I replace text in variable position with special character?

Answer: The formula is =REPLACE(B5, 1, FIND(“:”, B5), ” “), which generates text after special character (:).

Example 6: Generate numbers after special characters?

Question: How to generate numbers after special characters?

Answer: The formula is =REPLACE(B5, 1, FIND(“=”, B5), ” “), which generates numbers after equal (=) sign.

Example 7: Replace first two characters with space.

Question: How to replace the first two characters?

Answer: The formula is =REPLACE(B5, 1, 2, ” “), which replaced the first two characters with space.

Example 8: Replace numeric values in Excel:

Question: How to replace numbers in excel?

Answer: The formula is =REPLACE(B5, 7, 4, 2022), which replaced 2016 by 2022.

Example 9: Replace dates in excel:

Question: How can I replace date format?

Answer: The formula is =REPLACE(TEXT(B5, “dd-mmm-yy”), 4, 3, “March”), which replaced the date format.

Example 10: Insert hyphen (-) between numbers:

Question: How to insert hyphen (-) between numbers.

Answer: The formula is =REPLACE(REPLACE(B5, 4, 0, “-“), 8, 0, “-“), which insert hyphens (-) between numbers.

Example 11: Replace multiple text with REPLACE:

Question: How to replace multiple text in excel.

Answer: The formula is =REPLACE(REPLACE(B5, 1, 5, “Neymar”), 16, 6, ” Brazil”), which replaced two words–Messi and France.

More readings:

MS Excel official post

SUBSTITUE Function