Find a text string and substitute with a second text string.
The Excel SUBSTITUTE function replace the specific text in a text string that occurs in a specific location. The function replaces by selecting one or more instances of the occurrence of a text string.
For example, =SUBSTITUTE(“Butter”,”t”,”l”,2) return “Butler”, substitute 2nd instance “t” occurrence.
=SUBSTITUTE(text, old_text, new_text, [instance_num])
text – The text for which to substitute characters
old_text – The text to remove
new_text – The text to replace
instance [optional] – Specify the instance of the occurrence of old_text to be replaced. Otherwise if not supplied, every occurrence of old_text in the text is changed to new_text.
The SUBSTITUTE function is case-sensitive. We can use the REPLACE function to replace certain set of characters with worrying about case-sensitiveness.
If instance parameter does not opt for any value (not even blank or zero), every occurrence of old_text in the text is changed to new_text.
Blank or “Zero” argument of instance argument, will return #VALUE! error.
From SUBSTITUTE Function you can change Number suppose that you have value “453” but you notice it was not correct to replace with correct number you will use =SUBSTITUTE(“453”,5,6) it return “463”.