SUBSTITUTE Function

The SUBSTITUTE function is a way to perform a find‑and‑replace with a formula. 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.

Key features

• Replaces text by matching, not by position.
• Will replace all instances of matched text by default.
• Use instance_num to target only the 1st, 2nd, 3rd… match.
• Is case-sensitive and does not support wildcards.
• Works in all versions of Excel.

Because SUBSTITUTE doesn't support wildcards, it can't perform pattern matching. If you need pattern matching, see the REGEXREPLACE function, which brings the power of regular expressions to Excel formulas. If you want to replace text at a specific known position, see the REPLACE function.

Table of contents

• Example #1 – Quick demo
• Example #2 – Replace all
• Example #3 – Replace nth instance
• Example #4 – Replace line breaks
• Example #5 – Remove unwanted text
• Example #6 – Remove parentheses
• Related functions
• Notes

Example #1 - Quick demo

The SUBSTITUTE function is used to replace one text string with another using a generic syntax like this:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

Where text is the value to process (typically a cell reference), old_text is the text to find, new_text is the text to replace with, and instance_num is an optional argument to target only a specific instance of old_text by number. You can see how this works below. The formulas starting in cell D5 look like this:

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

Example #2 - Replace all

By default, the SUBSTITUTE function will replace all instances of one text string with another. You can see this behavior in the worksheet below, where we use SUBSTITUTE to replace periods (.) with hyphens (-). The formula in cell D5 looks like this:

=SUBSTITUTE(B5,".","-")

Notice the hyphen that already exists in cell B7 is unaffected.

Example #3 - Replace nth instance

By default, SUBSTITUTE will replace all instances of one text string with another. The optional fourth argument, called instance_num, can be used to replace just a specific instance. You can see this in the worksheet below, where we have configured SUBSTITUTE to replace only the second space with a hyphen. The formula in cell D5 copied down is:

=SUBSTITUTE(B5," ","-",2)

Notice that instance_num is provided as 2 to target the second space character.

Example #4 - Replace line breaks

SUBSTITUTE can be combined with other functions to solve more difficult problems. The example below uses the SUBSTITUTE function to replace line breaks with a comma and a space. This is a tricky problem because the line breaks aren't visible. However, because line breaks in Excel are ASCII character 10, we can use the CHAR function to inject a line break character for old_text inside SUBSTITUTE. The formula in cell D5 is:

=SUBSTITUTE(B5,CHAR(10),", ")

Example #5 - Remove unwanted text

You can use the SUBSTITUTE function to completely remove unwanted text by providing an empty string for the new_text argument. You can see this approach below, where we use SUBSTITUTE to strip all asterisks (*) from the numbers in column B. The formula in cell D5 looks like this:

=SUBSTITUTE(B5:B16,"*","")+0

Why add zero? Adding zero to the result forces Excel to convert text values to numbers when possible. You can see that it works in this case because the numbers in column D are now right-aligned. Also, notice we have provided the range B5:B16 as the text argument. Because we provide 12 values, SUBSTITUTE returns 12 results that spill into the range D5:D16.

Example #6 - Remove parentheses

SUBSTITUTE cannot replace more than one text string at a time. However, as a workaround, you can nest one SUBSTITUTE function inside another. You can see an example of this approach in the worksheet below, where we use SUBSTITUTE twice in one formula to remove the parentheses from the values in column B. This requires that we perform two replacements: one for the left parentheses and one for the right parentheses. The formula in D5 looks like this:

=SUBSTITUTE(SUBSTITUTE(B5,"(",""),")","")

This is an example of nesting one SUBSTITUTE inside another. The inner SUBSTITUTE runs first, replacing the left parenthesis with an empty string. The result is returned directly to the outer SUBSTITUTE, which replaces the right parentheses with an empty string. The final result in column D contains no parentheses.

See a more advanced example of this approach in a formula to normalize telephone numbers.

Related functions

Excel contains several functions that can help you find and replace text:

• REPLACE – Replace text by position when you know the starting point.
• FIND / SEARCH – Find the numeric position of text.
• REGEXREPLACE – Pattern‑based find and replace (Excel 365 only).

Notes

• SUBSTITUTE replaces old_text with new_text in a text string.
• By default, all instances of old_text are replaced with new_text.
• Instance_num limits replacement to a particular instance of old_text.
• SUBSTITUTE is case-sensitive and does not support wildcards.
• Use REGEXREPLACE (Excel 365) for more advanced replacement scenarios.
• SUBSTITUTE can only perform one replacement at a time.