Choosing SUBSTITUTE or REPLACE

Written by Allen Wyatt (last updated December 2, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

Gary needs to replace some text in a range of cells. He's seen some people suggest using the SUBSTITUTE function and others insist that the REPLACE function be used. Gary doesn't understand the difference between the two functions, so he's not sure which he should use.

These two functions process information in strings in different ways, so the correct answer is "it depends on what you want to do with your data." For instance, let's say that cell A1 contains the following text:

John Johnson lives in a white house on Wilson Street.

You could use the following SUBSTITUTE formula to affect the string:

=SUBSTITUTE(A1, "white", "yellow")

What you end up with is the following:

John Johnson lives in a yellow house on Wilson Street.

You have to be careful with the SUBSTITUTE function, though, because it will replace all instances of the text with what you specify. For instance, if you use this:

=SUBSTITUTE(A1, "John", "Robert")

You end up with the following:

Robert Robertson lives in a white house on Wilson Street.

It gave this result because there were two instances of "John" in the text, and both were replaced with "Robert".

The REPLACE function works differently. When using it, you don't specify what you want to replace, you specify where you want to do the replacement. For instance, consider the following:

=REPLACE(A1, 15, 2, "anguish")

What you end up with is the following:

John Johnson languishes in a white house on Wilson Street.

What happened is that the two characters beginning with the 15th character ("iv") are replaced with the text specified ("anguish").

The bottom line is that you would use REPLACE when you know the location of the text you want to replace, and you would use SUBSTITUTE when you know what you want to replace but don't know where it is within the text.

In both functions you can specify a range of cells as the first parameter and they will do their work on the entire range. You also need to keep in mind that SUBSTITUTE is case sensitive when it matches text.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7835) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing Chart Type

Excel allows you to add two distinct types of charts to your workbooks: embedded or chart sheets. You can switch between ...

Discover More

Creating Thin Spaces

Thin spaces are a typographic device that allows you add a bit of space between elements of a document. There are no thin ...

Discover More

Creating a Floating Macro Button

Macros can make your use of Excel much more powerful. If you have a macro that is triggered by an on-screen button, you ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Phantom Counts

Two common worksheet functions used to count things are COUNT and COUNTA. Not understanding how these functions treat ...

Discover More

Using a Week Number as One Criterion in a Formula

The SUMIFS function can be quite powerful in conditionally summing information based on criteria you specify. This tip ...

Discover More

Using the WEEKNUM Function

Need to know which week of the year a particular date falls within? Excel provides the WEEKNUM function so you can easily ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 5?

2023-12-02 08:40:11

Ron S

My suggestion was to consider using PowerQuery (PQ)

Consistently replacing text in data can be considered "data cleaning". Rather than embedding a formula into your data you can use PowerQuery. PQ automates data cleaning so that it can easily be applied as new data is added.

I am a huge fan of PQ automation. In general I now strongly suggest people learn the following features, at least at a high level to take advantage of all of the automation that has been built into Excel since 2007:

Excel Table > PowerQuery > (optional Data Manager) > Pivot Table > (specifically Slicer feature!) > Pivot Chart > use those features to generate "Dashboards"

1. (optional) Personally I prefer to first convert the raw data to an Excel table, it has some other benefits.
2. Load the data into PowerQuery.
3. Use the replace feature, if you have more than one replacement to do you can do it in separate steps
4. do any other data cleaning you need
5. Close and load back to Excel (on another tab is "best")
6 process your data from the PQ output, ie create PivotTables and PivotCharts that take full advantage of Excel Automation.
7. When you add new data you just need to use the PQ Data refresh to apply the changes and place them into the output that has been processed.


2023-12-02 07:20:44

Mike J

@Pat
Another solution might be to include a space.
=SUBSTITUTE(A1, "John ", "Robert "), but I suppose its use will depend on the actual data.

I am a little baffled by the statement:

"In both functions you can specify a range of cells as the first parameter and they will do their work on the entire range."

I cannot see how that works at all, unless maybe it's an array formula.


2023-12-02 06:15:03

Pat Nicholson

There are nuances in this case . . .

The Substitute function is more robust than this tip would let on. There's an optional argument allowing one to specify occurrence, so in the case presented here, you'd write =SUBSTITUTE(A1, "John", "Robert",1) and you'd get what you wanted.

Your point, however, is that the user would often make the assumption that substitute works on the "whole word" and not parse every single instance of the intended replacement string. Another issue is "case matching." The built in Ctl-H replace allows for both. Doing it one's self carries a bit of risk, but in the land of VBA, one has no choice.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.