The SUBSTITUTE function is to replace a specific text with another text string.
Formula:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Explanations:
– Text is required, which is the text containing the characters you want to substitute;
– Old_text is required, which is the text string that you want to replace;
– New_text is required, which is the text string that you use to replace with;
– [Instance_num] is optional, which is the occurrence of old_text you want to replace with. When it is omitted, it is assumed to be 1.
Cautions:
[Instance_num] is the occurrence of the old_text, not the position of the starting character. For example, the occurrence of the 2nd letter "U" in "SUBSTITUTE" is 2, and the position of the 2nd letter "U" is 8.
Example 1: Replace "fun" with "Fun" in the text string "SUBSTITUTE function".
=SUBSTITUTE(B1,"fun","Fun")
The result returns "SUBSTITUTE Function".
Example 2: Replace the 3rd "u" with "U" in the text string "Substitute function".
=SUBSTITUTE(B1,"u","U", 3)
The result returns "Substitute fUnction".
