Difference between REPLACE and SUBSTITUTE in Microsoft Excel

REPLACE and SUBSTITUTE are two common functions with quite similar names in Excel. However, the way to use REPLACE & SUBSTITUTE is different.

Microsoft Excel is a flexible tool that prides itself on providing users with a wide range of useful data analysis functions. But if you're still learning how to work with spreadsheets, and finding the right function for the job at hand can be challenging, some function names can also confuse you about how to actually use them.

If you're trying to change the text in a cell, you might be confusing the REPLACE and SUBSTITUTE functions. This article will help you solve that problem.

Use the REPLACE function in Excel

Suppose there is a list of manufacturer information for products on sale. Some of the information in the table is old, so a bit of data need to be changed to update it.

The REPLACE function changes certain characters in the contents of certain cells with a different set of text. The replacement text can be of any length and it will be set exactly to the number of characters after the start of the original text string.

Assuming there's a new way to report production code, this spreadsheet needs to be updated to incorporate that.

1. Select cell D7 , the cell below says New code .

2. In the cell itself or the formula bar, enter or paste the formula below:

=REPLACE(B2,2,3,"PTM")

3. Press Enter .

Inside this cell, the example just took the text from cell B2 ("6418229"), and starting at the 2nd character (number 4), has been replaced 3 characters (number 418) with the character PTM (equivalent to the PTM character). equivalent to Portland Mechanics).

The replacement text need not be the same length as the number of characters to be replaced. If you only want to replace one character, you can change the 3 in the formula above to 1, ending with the 9-character string "6PTM18229" in cell D7.

Using the SUBSTITUE function in Excel

In this example, suppose Portland Mechanics was later acquired and renamed. You know what you're looking for, so you can use the SUBSTITUE function to replace specific text with another new text.

1. Select cell D4, which is under Updated brand .

2. In that cell or the formula bar, type or paste the formula below:

=SUBSTITUTE(A2,"Portland","Anodyne")

3. Press Enter .

Inside this cell, the example just took the text from cell A2 and replaced the word Portland with Anodyne, creating a new name - Anodyne Mechanics in cell D4 .

Suppose you leave a note explaining the name change for the next viewer of the document and want to save time by using the SUBSTITUE function. When you edit it in cell G5 to refer to cell G3 as shown below, you will see all the words Portland replaced with Anodyne.

This is not what the example wants in this case, so it is necessary to add [instance_num] to specify that only one of the locations Portland appears to be replaced.

To do this, select cell G7 and enter this formula in the cell or formula bar:

=SUBSTITUTE(G3,"Portland","Anodyne",2)

Adding the number 2 tells Excel that it only wants to change the second position of the word Portland.

Although the names are similar, the REPLACE and SUBSTITUE functions have different uses. If you know the exact location of your new text, use REPLACE. However, if you know the words you intend to replace or need to replace multiple versions of the same content in a cell or in multiple cells, SUBSTITUTE is a better choice.

Lesley Montoya

Update 27 June 2023