SUBSTITUTE Function

Think of SUBSTITUTE as a way to run a find‑and‑replace in a single cell with a function. Use it when you know what text you want to change, but you don’t know (or care) where it appears in a text string. By default, SUBSTITUTE will replace all instances of a text string with another text string. Optionally, you can specify which instance of text to replace by providing a number. To completely remove matched text (old_text), enter an empty string ("") for the new_text argument. Here are some important notes and limitations:

• SUBSTITUTE replaces text by matching, not by position.
• SUBSTITUTE will replace all instances of matched text by default.
• The instance argument lets you target a specific instance of text to substitute.
• SUBSTITUTE is case-sensitive and does not support wildcards.
• SUBSTITUTE is an older function that works in all versions of Excel.

Because SUBSTITUTE doesn't support wildcards, it can't really do pattern matching. If you need pattern matching, see the REGEXREPLACE function, which brings the power of regular expressions to Excel formulas. However, REGEXREPLACE only works in the latest version of Excel. 

Examples

Below are the formulas used in the example shown above:

=SUBSTITUTE(B6,"t","b") // replace all t's with b's
=SUBSTITUTE(B7,"t","b",1) // replace first t with b
=SUBSTITUTE(B8,"cat","dog") // replace cat with dog
=SUBSTITUTE(B9,"#","") // replace # with nothing

The SUBSTITUTE function cannot replace more than one string at a time. However, SUBSTITUTE can be nested inside of itself to accomplish the same thing. For example, with the text "a (dog)" in cell A1, the formula below will strip parentheses () from the text:

=SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","") // returns "a dog"

This same approach can be used in a more complex formula to normalize telephone numbers.

Related functions

Use the REPLACE function to replace text at a known location in a text string. Use the SUBSTITUTE function to replace text by searching when the location is not known. Use FIND or SEARCH to determine the location of specific text. Use the REGEXREPLACE function to find and replace text based on sophisticated pattern matching.

Notes

• SUBSTITUTE finds and replaces old_text with new_text in a text string.
• Instance limits replacement to a particular instance of old_text.
• When instance is omitted, all instances of old_text are replaced with new_text.
• SUBSTITUTE is case-sensitive and does not support wildcards.