Exceljet

Quick, clean, and to the point

Excel SUBSTITUTE Function

Excel SUBSTITUTE function
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])
Arguments 
  • 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.
Version 
Usage notes 

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 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 SUBSTITUTE replacement 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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.