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

SUBSTITUTE Function in Excel 1

  • 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

SUBSTITUTE Function in Excel 2

previous-small SUBSTITUTE Function in Excel                                                                                                                next_small SUBSTITUTE Function in Excel