REPLACE vs. SUBSTITUTE in Microsoft Excel: What’s the Difference?

Microsoft Excel is a highly versatile tool boasting a variety of functions for analyzing your data. But if you’re still learning the ropes of spreadsheets, finding the right function for the task you’re trying to do can be challenging. Some function names can leave you puzzled as to what they actually do.

If you’re trying to change certain text in a cell, you might be confused by the fact that there’s a REPLACE function and a SUBSTITUTE function that sound like they do the same thing. So what’s the difference?

Using the REPLACE Function in Excel

Let’s say we have a list of manufacturer information for products we’re selling. It’s a little out of date, so we’ll need to change some of the data to update it.

The REPLACE function changes a specified number of characters within a particular cell’s text with another set of text. The replacement text can be any length, and it will be placed exactly the number of characters after the start of the original text string.

Let’s say we’ve found out there’s a new way our company is reporting manufacturing codes, and we need to update this sheet to incorporate that.

1. Select cell D7, the cell below the “New code” notation.
2. In either the cell itself or the formula bar, type or paste the formula below:

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

3. Press Enter.

Inside the cell, we’ve just taken the text from cell B2 (“6418229”), and, starting from the second character (the number “4”), we’ve replaced three characters (the digits “418”) with the letters “PTM” (for “Portland Mechanics”).

The replacement text does not have to be the same length as the number of characters being replaced; if we only wanted to replace one character, we could change the “3” in the above formula to a “1”, ending up with the nine-character string “6PTM18229” in cell D7.

Using the SUBSTITUTE Function in Excel

For the sake of this example, let’s say we later find out that Portland Mechanics has been acquired and has changed their name. We know exactly what we’re looking for, so we can use the SUBSTITUTE function to replace specific text with new, different text.

1. Select cell D4, the cell below the “Updated brand” notation.
2. In either the cell itself or the formula bar, type or paste the formula below:

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

3. Press Enter.

Inside the cell, we’ve just taken the text from cell A2 (“Portland Mechanics”), and replaced the word “Portland” with the word “Anodyne”, creating the new name “Anodyne Mechanics” in cell D4.

Let’s say we leave a note explaining the name change for the next person who views the document, and want to save time by using the same SUBSTITUTE function. When we edit it in cell G5 to refer to cell G3, as shown below, we notice that all the appearances of the word “Portland” are replaced with “Anodyne”.

This isn’t the behavior we want in this case, so we’ll have to add the [instance_num] syntax to specify that we only want one of the times “Portland” appears—one “instance”—to be replaced.

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

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

The extra “2” tells Excel we only want the second instance to be changed. There are also more complex ways to use the SUBSTITUTE function, such as with nested functions or IF statements—or even IF formulas with nested statements, if you’re really catching onto things.

Different Functions, Different Uses

While they are similarly named, the REPLACE and SUBSTITUTE functions shine in different use cases. If you know exactly where your new text is going, it’s likely REPLACE is the better option. However, if you know what words you’re replacing instead, or need to replace multiple instances of the same text, either in a single cell or across multiple cells, the SUBSTITUTE option will likely work better for you.

When you’re looking to replace text in a cell, now you know the difference between these similarly-named functions, and how to best use them in your data. If you’re looking to change text within their original cells, consider other ways of finding and replacing text within Excel.