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
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.
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!
You may use Excel's trigonometric functions to do some quick calculations, and suddenly notice that the results in your ...
Discover MoreProgrammers know that a staple of any language is the ability to create conditional statements. Excel understands this, ...
Discover MoreFunctions are at the heart of Excel's power in working with data. One of the most misunderstood functions provided by ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments