How to use SUBSTITUTE function in Excel 2013

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.