SUBSTITUTE Function in Excel – Substitute the Values in Excel

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.

Result