Table of Contents
Summary
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.
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.
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.
=SUBSTITUTE(C6, I6, J6)
The result returned in cell K6 is “Claire Gute”
