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
Count specific characters in a range
For each cell in the range, SUBSTITUTE removes all the o's from the text, then LEN calculates the length of the text without o's. This number is then subtracted from the length of the text with o's. Because we are using SUMPRODUCT, the result of all this calculation is a list of items (an array),...
Count line breaks in cell
First, the LEN function counts total characters in the cell B5. Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of a second LEN, which counts characters without...
Extract nth word from text string
In this example, the goal is to extract the nth word from the text string given in column B. The article below explains two approaches. The first approach is based on the new TEXTSPLIT function , which makes it very easy to split text with a custom delimiter. The second approach is a more...
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...
Find and replace multiple values
There is no built-in function to perform a series of find and replace operations in Excel, but this article explains how you can cobble together a formula that does the same thing. The text to look for and replace with is stored directly on the worksheet in a table, and retrieved with the INDEX...
Split dimensions into three parts
In this example, the goal is to split the text strings in column B, which contain three dimensions in the form "L x W x H", into 3 separate dimensions. One problem with dimensions entered as text is that they can't be used for any kind of calculation. So, in addition, we want our final dimensions...
Extract word containing specific text
The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess. Working from the inside out, the original text in B5 is flooded with spaces using SUBSTITUTE:...
Clean and reformat telephone numbers
In this example, the goal is to clean up telephone numbers with inconsistent formatting and then reformat the numbers in the same way. In practice, this means we need to start by removing the extra non-numeric characters, including spaces, dashes, periods, and parentheses. Once these characters are...
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...
Get top level domain (TLD)
In this example, the goal is to extract the top-level domain (TLD) from a list of domains. A top-level domain is the last segment of text in a domain name, for example, ".com", ".net", or ".net". In the current version of Excel, the TEXTAFTER function is a simple way to solve this problem. In an...
Get workbook name and path without sheet
In this example, the goal is to get a normal path to the current workbook, without a sheet name, and without the square brackets ("[ ]") that normally enclose the workbook name. This is a pretty simple problem in the latest version of Excel, which provides the TEXTBEFORE function . In older...
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)...
Get last word
This formula is an interesting example of a "brute force" approach that takes advantage of the fact that TRIM will remove any number of leading spaces. Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100 spaces: SUBSTITUTE(B6...
Text split to array
In this example, the goal is to split the text strings in column B into an array of values. In a problem like this, we need to identify the delimiter , which is the character (or characters), that separate each value we want to extract. In this case, each text string in column B contains comma-...
Convert column number to letter
In this example, the goal is to convert an ordinary number into a column reference expressed in letters. For example, the number 1 should return "A", the number 2 should return "B", the number 26 should return "Z", etc. The challenge is that Excel can handle over 16,000 columns, so the number of...
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".
