Excel SUBSTITUTE Function (10 Examples)

Excel SUBSTITUTE Function

The Excel SUBSTITUTE Function replaces existing text with new text defined by the users. Excel users use the SUBSTITUTE function to replace specific text in a text string. To replaced text in a specific location in a text string, users should use Excel REPLACE Function.

Example list:

---

1. How to substitute a single word with a new word.
2. How to substitute the first instance of a number with a new number.
3. How to substitute the third instance of a text with a new text.
4. How to replace special character with nothing.
5. How to remove unnecessary spaces between text.
6. How to substitute multiple values with a single formula [Nested SUBSTITUTE]
7. How to substitute full stops by comma.
8. How to substitute a date format.
9. How to extract phone numbers from detail information. [Advanced Level]
10. How to replace multiple names with another names. [Advanced Level]

---

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_num])

where,

1. Text: This is the text or the reference of the text cell, which is a required argument.
2. Old_text: The old text that users want to replace. It is a required argument.
3. New_text: The text by which users want to replace the old text, and it is also a required argument.
4. Instance_num: This is an optional argument, which specifies which occurrence of old_text users want to replace with new_text. Users specify instance_num, only that instance of old_text will be replaced; otherwise, every occurrence of old_text in text is changed to new_text.

Example 1: Substitute a single word

Question: How can I substitute a single word?

Answer: The formula is =SUBSTITUTE(B4, “hate”, “love”), which substitutes the word “hate” by the word “love“

Example 2: Substitute first occurrence of a number with a new number:

Question: How can I substitute the first occurrence of a number with a new number?

Answer: The formula is =SUBSTITUTE(B4, “1”, “7”, 1), which replaced the first occurrence of 1.

Example 3: Substitute the THIRD instance of a text with a new text:

Question: How can I replace the third occurrence of a text?

Answer: The formula is =SUBSTITUTE(B4, “i”, “*”, 3), which replaced the third occurrence of i (in main).

Example 4: Replace special character with nothing:

Question: How can I replace the special characters (**) with nothing?

Answer: The formula is =SUBSTITUTE(B4, “*”, “”), which replaced ** with nothing.

Example-5: Remove unnecessary spaces between text:

Question: How to remove unnecessary space between texts?

Answer: The formula is =SUBSTITUTE(B5, ” “, “”), which replaced unnecessary spaces with nothing.

Example -6: Replace multiple texts with a single SUBSTITUTE formula:

Question: How can I replace multiple text with a single SUBSTITUTE Function?

Answer: The formula is =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5, “NY”, “New York”), “NJ”, “New Jersey”), “CT”, “Connecticut”), “NM”, “New Mexico”), “CA”, “California”), “NV”, “Nevada”), “OR”, “Oregon”), “AL”, “Alabama”), which replaced the short names of states by the full names of them.

Example-7: Replace full stops by commas:

Question: How can I replace FULL STOPS by COMMAS?

Answer: The formula is =SUBSTITUTE(C5, “.”, “,”), which replaced the full stops by commas.

Example-8: Substitute a date style with another style:

Question: How can I replace the date format of 03.23.2022 to 3/23/2022?

Answer: The formula is =SUBSTITUTE(C5, “.”, “/”), which replaced the full stops with forward slash.

Example -9: Extract Phone Numbers from detail information:

Question: How can I extract phone numbers from detail information?

Answer: We will solve this problem in three steps: In step 1, we generate the numbers from C5 to D5 by using the formula =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(C5, LEN(C5)-FIND(” “, C5))), ” “, “”), “-“, “”), “(“,””), “)”,””),”-“, “”).

In step 2, we convert the cell D5 into number by multiplying the formula by 1 as follows:

In step 3, the final step, we select the cells D5-D9, then right-click–> Format Cells–> Click on Special–> Click on Phone Number –> Finally click OK.

Example-10: Replace multiple Names with another Names:

Question: How can I replace multiple names with a single SUBTITUTE formula?

Answer: The formula is =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5, “Wheatley”, “White”), “Merker”, “Brody”), “Craig”, “Gary”), “Parker”, “Messi”), “Sr.”, “Jr.”), which replaced 5 names.

Conclusion:

The Excel SUBSTITUTE function is used when users want to replace text based on matching, not position whereas the excel REPLACE function replaces text based on position. Users can also specify how many stances they want to replace a specific text. The SUBSTITUTE function is case sensitive and cannot count negative instances.

More readings:

MS Official Post

REPLACE Function