Excel SUBSTITUTE function


The Excel SUBSTITUTE function replaces existing test with new text in a text string. This function is useful for inserting text and/or correcting misspellings in Excel.

The SUBSTITUTE function is an Excel text function.


=SUBSTITUTE(text, old_text, new_text, instance_num)

Arguments (inputs)

text = the original text string to be updated, can be a value or cell reference

old_text = the specific text string to be replaced by the new_text, can be a value or cell reference

new_text = the text string to replace the old_text

instance_num = [optional] the number of times to replace old_text with new_text in the text string, if omitted all instances will be replaced

Return value

The SUBSTITUTE function returns an updated text string, where the old_text has been replaced by the new_text.


Excel SUBSTITUTE function example

In the example above the SUBSTITUTE function corrects the spelling of the misspelled first names in column C and returns the correct customer names in column K. In cell K6, the SUBSTITUTE function replaces the old_text (misspelling “Claure”) with the new_text (“Claire”). The fourth argument, instance_num, is omitted so all instances of “Claure” will be replaced.


The result returned in cell K6 is “Claire Gute”