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 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.
SUBSTITUTE function examples
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...
Sum numbers in single cell
The goal is to sum numbers that appear inside a single cell as seen in column B. Technically, the numbers in each cell are a single text string, and the numbers are separated by commas, which is referred to as a "delimiter". In the current version of Excel, the easiest way to solve this problem is...
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...
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...
Count total words in a cell
In this example, the goal is to count the total number of words in a cell. Excel doesn't have a dedicated function for counting words. However, with a little ingenuity, you can create a formula to perform this task using a combination of built-in functions. In newer versions of Excel, the best...
Join cells with comma
Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (...
Extract last two words from cell
At the core, this formula uses the MID function to extract characters starting at the second to last space. The MID function takes 3 arguments: the text to work with, the starting position, and the number of characters to extract. The text comes from column B, and the number of characters can be...
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...
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...
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 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...
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 nth occurrence of character
In this example we are looking for the nth occurrence of the "@" character. Working from the inside out, we first use the SUBSTITUTE function to replace the nth occurrence of "@" with CHAR(160): SUBSTITUTE(B5,"@",CHAR(160),C5) The SUBSTITUTE function has an optional 4th argument called instance...
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...
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".
