How to Replace Text with the SUBSTITUTE Formula in Excel

Sometimes, you need to change text in a systematic way: If you want to replace text with new text, there are two options. If you only want to do it once, using the Find-and-Replace dialogue is probably the fastest choice. If you want to do it repeatedly or don’t want to mess with your input data, you should try the SUBSTITUTE formula.

How to use the SUBSTITUTE formula

The SUBSTITUTE formula replaces parts of text. It searches for some text (“string”) within another text and once found replaces it.

The SUBSTITUTE formula has 4 parts:

1. The complete text or cell containing text. From this text you want to replace some parts.
2. The text you look for and want to replace by new text.2) The text you look for and want to replace by new text.
3. The new text, which should replace the old text.
4. You can further define, which instance of the old text you want to replace. E.g. the first or second instance. If you leave this part blank, every instance of your old text will be replaced.

Examples for the SUBSTITUTE formula

Let’s use this knowledge and take a look at some examples.

Example 1: Simple SUBSTITUTE application

Say you have the text “This cell contains text” in cell B3. You want to search for the text given in cell C3 (“text”) and replace it by the text in cell D4 (“some text”). So, you add the word “some” in front of text.

The complete formula looks like this:

=SUBSTITUTE(B3,C3,D3)

Example 2: Replace the last instance

In a second example, you want to replace only the last occurrence of some text. Again, you can use the SUBSTITUTE formula. But instead of leaving the last argument blank, you must specify that you want to replace the last instance. Therefore, you count the number of instances. This can be done with a trick, also using the SUBSTITUTE formula.

The first three arguments are like the previous example:

1. The text from which you want to replace some parts is given in cell B4.
2. You search for “text” again. This is provided in cell C4.
3. The replacement: “some words” is given in cell D4.
4. The last argument is a little bit tricky. There is no formula in Excel which counts the number of occurrences of a text string within another text string. That’s why you use a workaround: You count the number of letters of the original, complete text. Then you use the SUBSTITUTE formula to replace your “Old Text” with nothing. You count the number of letters again. If you subtract now the number of letters without your search text from the number of letters of the complete text string, you got exactly the number of letters of all your search text. Divide this number by the length of the search text and you got the number of occurrences. The last argument of the formula is:

(LEN(B4)-LEN(SUBSTITUTE(B4,C4,””)))/LEN(C4) 

Now you can combine it to the complete formula:

=SUBSTITUTE(B4,C4,D4,(LEN(B4)-LEN(SUBSTITUTE(B4,C4,””)))/LEN(C4))

Difference between REPLACE and SUBSTITUTE

The difference to the REPLACE formula: The REPLACE formula solves a similar purpose like the SUBSTITUTE formula. But instead of letting Excel search for a certain string, you must provide the start number of characters you want to replace as well as the total number of character to be replaced.