SUBSTITUTE is very useful function in Excel when we want to replace specific text in a text string with some other text.
Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
- text: The original text string containing the text to be replaced
- old_text: The text/data you want to replace with new_text
- new_text: The text/data that is used to replace the old_text
- [instance_num]: An option which specifies which occurance of old_text you want to replace
- If [instance_num] is specified, just that instance of the old_text is replaced;
- Otherwise, all instances of old_text are replaced with new_text.
IMPORTANT NOTES:
- The SUBSTITUTE function is Case Sensitive.
- The function can take texts, numbers as well as cell references for replacement.
- Text strings should always be placed using double quotes (eg. “Tom") while the reference cell formula (eg. C4) do not need double quotes. For numbers, using double quotes is optional.
- The output generated will always in text format, even if the input data were in number format.
