SUBSTITUTE Function in Excel substitutes one or more instances of a given text string with another string.
Syntax of SUBSTITUTE Function in Excel:
SUBSTITUTE( text, old_text, new_text, [instance_num] )
Where,
- text – The original text string, that you want to replace/substitute a part of.
- old_text – The sub-string that is to be found and replaced by
- new_text –The new text string that is to be used to replace the
- [instance_num] – An optional argument which specifies which occurrence of old_text should be replaced by the new_text.
- If a value of [instance_num] is specified, just that instance of the old_text is replaced
- Otherwise, all instances of old_text are replaced with the new_text.
Example of SUBSTITUTE Function in Excel:
Formula
- In Row number 2, SUBSTITUTE Function substitutes “a” with “i” in all the occurrences
- In Row number 3, SUBSTITUTE Function replaces “a” with “i” in the second occurrence.
And so on, so the result will be.