Replacing Text With SUBSTITUTE

Data often needs find-and-replace inside formulas: stripping dashes from phone numbers, swapping a word, or removing stray symbols.

The SUBSTITUTE function finds a piece of text inside a cell and replaces every occurrence with new text, all without changing the original cell.

It is the formula-based equivalent of Find & Replace, but it updates automatically as your data changes.

SUBSTITUTE takes up to four arguments:

• text the original string or cell
• old_text the text to find
• new_text the text to put in its place
• instance_num (optional) which occurrence to replace

The shape is =SUBSTITUTE(text, old_text, new_text, [instance_num]). If you omit the last argument, all occurrences are replaced.