Summary

The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.

Purpose 

Replace text based on content

Return value 

The processed text

Syntax

=SUBSTITUTE(text,old_text,new_text,[instance])
  • text - The text to change.
  • old_text - The text to replace.
  • new_text - The text to replace with.
  • instance - [optional] The instance to replace. If not supplied, all instances are replaced.

How to use 

The Excel SUBSTITUTE function can replace text by matching. Use the SUBSTITUTE function when you want to replace text based on matching, not position. Optionally, you can specify the instance of found text to replace (i.e. first instance, second instance, etc.).

SUBSTITUTE is case-sensitive. To replace one or more characters with nothing, enter an empty string ("").

Examples

Below are the formulas used in the example shown above:

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

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.

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.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.