The 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 Formula 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.
Leave a Reply