What is the Excel SUBSTITUTE Function?
The excel SUBSTITUTE function replaces the old text to the new text by matching the same word. The SUBSTITUTE function works in case sensitive and has no support for a wildcard character.
SUBSTITUTE Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])
text, required, the text for which you want to change.
old_text, required, the text to replace.
new_text, required, the text to replace with.
instance_num, optional, which old_text occurrence you want to replace with new_text. If omitted, Excel replaces every occurrence of old_text to new_text.
How to Use SUBSTITUTE Function in Excel
The following is an example of the SUBSTITUTE function usage and the results.
SUBSTITUTE Function #1
=SUBSTITUTE(A2,B2,C2)
The instance_num argument is optional; if you omit the argument, then excel replaces all “g” letters to “d” letter.
The result is a doodle.
SUBSTITUTE Function #2
=SUBSTITUTE(A3,B3,C3,D3)
If you put number 1 in instance_num argument, then excel only replace the first “g” letter and ignores all the “g” letters afterward.
The result is doogle.
SUBSTITUTE Function #3
=SUBSTITUTE(A4,B4,C4)
There are two “the” words, first at the beginning of the sentence with the large “T” letter. Second in the middle of the sentence with all lowercase letters.
The SUBSTITUTE function works in case sensitive, replace only the word with the same letter. Excel replaces the second “the” word.
The result is The Quick Brown Fox Jumps Over a Lazy Dog.
SUBSTITUTE Function #4
=SUBSTITUTE(A5,B5,C5)
There is no “THE” (all uppercase) word, the result has nothing to change, just like the text in cell A5.
SUBSTITUTE Function #5
=SUBSTITUTE(A6,B6,C6,D6)
The SUBSTITUTE function has no support for wildcard character. If there are “?” (question mark) or “*” (asterisk) character, Excel considers as “ordinary” character instead of a wildcard character.
There is no “g???” word in cell A6, the results remain unchanged, like the text in cell A6.
Difference between SUBSTITUTE and REPLACE in Excel
Besides the SUBSTITUTE function, Excel has another function to change the text, namely the REPLACE function. What is the difference between the SUBSTITUTE function and the REPLACE function?
The SUBSTITUTE function changes the text based on the specified word, while the REPLACE function changes the text based on the specified position.
If you know the word to be changed, use the SUBSTITUTE function. If you know the position of the text to be changed, use the REPLACE function.
For a more detailed explanation of the Excel REPLACE function, please read the following article:
