Excel SUBSTITUTE Formula

The Excel SUBSTITUTE function replaces new_text for old_text in a text string.

The Syntax is:

SUBSTITUTE(text,old_text,new_text,[instance_num])

The arguments can be explained as:

• text – cell containing the characters you want to replace.
• old_text – the text to be replaced.
• new_text – the new text you want inserted.
• Instance_num – if omitted, it replaces every occurrence of the old_text; Otherwise, only the specified occurrence of the old_text is replaced in the text string .

Note: all arguments are required except those with [square brakets] which are optional.

SUBSTITUTE Formula Example

Taking the formula displayed in cell B18 in English it reads:

Locate the text in cell A18, and replace the 15, with 20.

More SUSTITUTE Function Examples

Here I nest SUBSTITUE functions to replace two different characters at once.

And as an alternative to SUBSTITUTE you can use the CLEAN formula with the CHAR function to remove the non-printing ASCII characters 0 to 32.