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

Unwanted Styles

Want to get rid of some styles in a document that you don't need any more? It can be a difficult thing to do, unless you ...

Discover More

Changing Body Text to a Heading

When working on a document in Outline view, you may need to change regular body text to a heading in the outline. It's ...

Discover More

Inserting the User's Name

Word keeps track of a name for the person using the program. If you want to add this person's name into the document, ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Nesting IF Worksheet Functions

The IF worksheet function is very handy to make conditional evaluations. You are not limited to a single IF comparison, ...

Discover More

Using the CONCATENATE Worksheet Function

The process of combining string (text) values to make a new string is called concatenation. Excel provides the ...

Discover More

Understanding the LET Function

The LET function provides an easy way to define and use variables within a formula. This tip shows you how you can start ...

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 3 + 0?

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.