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.
SUBSTITUTE function examples
Remove last word
This formula uses the MID function to remove the last word from a text string. The main challenge is to figure out where the last word begins. The formula is a bit convoluted, but the steps are simple. We first count how many spaces exist in the text using LEN and SUBSTITUTE. Next, we use the...
Get last name from name
In this example, the goal is to extract the last name from names that appear in <first> <middle> <last> format, where the middle name is not always present. The easiest way to do this is with the newer TEXTAFTER function. In older versions of Excel, you can use a significantly...
Count specific words in a range
In the generic version of the formula, rng represents the range to check, and txt is the word or substring to count. In the example shown, B5:B8 is the range to check, and C2 contains the text (word or substring) to count. For each cell in the range, SUBSTITUTE removes the substring from the...
LAMBDA count words
The LAMBDA function can be used to create reusable, custom functions in Excel without VBA or macros. The first step in creating a LAMBDA function is to verify the formula logic needed in a standard Excel formula. In this example, the base formula is: =LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(...
Get last line in cell
This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and "flood" the text with spaces where we find one. Then we come back and grab text from the right. Working from the inside out, we use the SUBSTITUTE function to find all line...
Remove unwanted characters
The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which effectively removes the character completely. How can you figure out which character(s)...
Split text with delimiter
The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get rid of the extra space. In this snippet, the delimiter ( delim ) is replaced with...
Replace one delimiter with another
In this example, the goal is to replace the comma separated values in column B with the line break separated values seen in column D. In a problem like this, the first step is to identify the delimiter , which is the character (or characters), that separate each value we want to process. In this...
Position of 2nd 3rd etc instance of character
At the core, this formula uses the fact that the SUBSTITUTE function understands "instance", supplied as an optional fourth argument called "instance_num". This means you can use the SUBSTITUTE function to replace a specific instance of a character in a text string. So: SUBSTITUTE(B4,"x","~",D4)...
Replace one character with another
The SUBSTITUTE function is full automatic. All you need to do is supply "old text" and "new text". SUBSTITUTE will replace every instance of the old text with the new text. If you need to perform more than one replacement at the same time, you'll need to nest multiple SUBSTITUTE functions. See this...
Count specific characters in text string
In this example, the goal is to count the number of occurrences of a character in a cell or text string. Strangely, Excel does not have a function dedicated to counting characters, so we need to use a formula that computes a count manually. The typical way to do this is to use a formula based on...
Convert feet and inches to inches
In this example the goal is to parse feet and inches out in the text strings shown in column B, and create a single numeric value for total inches. The challenge is that each of the two numbers is embedded in text. The formula can be divided into two parts. In the first part of the formula, feet...
Split dimensions into two parts
Background A common annoyance with data is that it may be represented as text instead of numbers. This is especially common with dimensions, which may appear in one text string that includes units, for example: 50 ft x 200 ft 153 ft x 324 ft Etc. In a spreadsheet, it's a lot more convenient to have...
Trim text to n words
We need a way to split text at a certain marker that corresponds to a certain number of words. Excel doesn't have a built-in function to parse text by word, so are using the SUBSTITUTE function's "instance" argument to replace an "nth space" character with the pound sign (#), then using FIND and...
Extract word that begins with specific character
Starting from the inside out, the MID function is used to extract all text after "@": MID(B5,FIND("@",B5),LEN(B5)) The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text. This is a bit sloppy, but it avoids having to calculate the...
SUBSTITUTE function videos
How to count characters with the LEN function
Excel has a simple function called LEN, for length, that calculates the number of characters in a text string which is a surprisingly useful function. Let's take a look. The LEN function takes just one argument: the text you want to count. If I supply the address B5, which contains the text "Susan...
How to build all-in-one formulas
In this video we're going to build a formula that counts the number of words in a cell. This cell has eight words in it, and we're going to build a formula step by step that gives us the number "8". We're going to use "helper" formulas to do that, and when we're finished, we'll take those helper...
Related functions
REPLACE Function
The Excel REPLACE function replaces characters specified by location in a given text string with another text string. For example =REPLACE("XYZ123",4,3,"456") returns "XYZ456".
