In this tutorial, we will look at several ways to find and replace multiple words, strings, or individual characters, so you can choose the one that best suits your needs.
How do people usually search in Excel? Mostly, by using the Find & Replace feature, which works fine for single values. But what if you have tens or even hundreds of items to replace? Surely, no one would want to make all those replacements manually one-by-one, and then do it all over again when the data changes. Luckily, there are a few more effective methods to do mass replace in Excel, and we are going to investigate each of them in detail.
The easiest way to find and replace multiple entries in Excel is by using the SUBSTITUTE function.
The formula's logic is very simple: you write a few individual functions to replace an old value with a new one. And then, you nest those functions one into another, so that each subsequent SUBSTITUTE uses the output of the previous SUBSTITUTE to look for the next value.
In the list of locations in A2:A10, suppose you want to replace the abbreviated country names (such as FR, UK and USA) with full names.
To have it done, enter the old values in D2:D4 and the new values in E2:E4 like shown in the screenshot below. And then, put the below formula in B2 and press Enter:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10, D2, E2), D3, E3), D4, E4)
…and you will have all the replacements done at once:
Please note, the above approach only works in Excel 365 that supports dynamic arrays.
In pre-dynamic versions of Excel 2019, Excel 2016 and earlier, the formula needs to be written for the topmost cell (B2), and then copied to the below cells:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)
Please pay attention that, in this case, we lock the replacement values with absolute cell references, so they won't shift when copying the formula down.
Note. The SUBSTITUTE function is case-sensitive, meaning you should type the old values (old_text) in the same letter case as they appear in the original data.
As easy as it could possibly be, this method has a significant drawback - when you have dozens of items to replace, nested functions become quite difficult to manage.
Advantages: easy-to-implement; supported in all Excel versions
Drawbacks: best to be used for a limited number of find/replace values
In situation when you are looking to replace the entire cell content, not its part, the XLOOKUP function comes in handy.
Let's say you have a list of countries in column A and aim to replace all the abbreviations with the corresponding full names. Like in the previous example, you start with inputting the "Find" and "Replace" items in separate columns (D and E respectively), and then enter this formula in B2:
=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)
Translated from the Excel language into the human language, here's what the formula does:
Search for the A2 value (lookup_value) in D2:D4 (lookup_array) and return a match from E2:E4 (return_array). If not found, pull the original value from A2.
Double-click the fill handle to get the formula copied to the below cells, and the result won't keep you waiting:
Since the XLOOKUP function is only available in Excel 365, the above formula won't work in earlier versions. However, you can easily mimic this behavior with a combination of IFERROR or IFNA and VLOOKUP:
=IFNA(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), A2)
Note. Unlike SUBSTITUTE, the XLOOKUP and VLOOKUP functions are not case-sensitive, meaning they search for the lookup values ignoring the letter case. For instance, our formula would replace both FR and fr with France.
Advantages: unusual use of usual functions; works in all Excel versions
Drawbacks: works on a cell level, cannot replace part of the cell contents
For Microsoft 365 subscribers, Excel provides a special function that allows creating custom functions using a traditional formula language. Yep, I'm talking about LAMBDA. The beauty of this method is that it can convert a very lengthy and complex formula into a very compact and simple one. Moreover, it lets you create your own functions that do not exist in Excel, something that was before possible only with VBA.
For the detailed information about creating and using custom LAMBDA functions, please check out this tutorial: How to write LAMBDA functions in Excel. Here, we will discuss a couple of practical examples.
Advantages: the result is an elegant and amazingly simple to use function, no matter the number of replacement pairs
Drawbacks: available only in Excel 365; workbook-specific and cannot be reused across different workbooks
To replace multiple words or text in one go, we've created a custom LAMBDA function, named MultiReplace, which can take one of these forms:
=LAMBDA(text, old, new, IF(old<>"", MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))
=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))
Both are recursive functions that call themselves. The difference is only in how the exit point is established.
In the first formula, the IF function checks whether the old list is not blank (old<>""). If TRUE, the MultiReplace function is called. If FALSE, the function returns text it its current form and exits.
The second formula uses the reverse logic: if old is blank (old=""), then return text and exit; otherwise call MultiReplace.
The trickiest part is accomplished! What is left for you to do is to name the MultiReplace function in the Name Manager like shown in the screenshot below. For the detailed guidelines, please see How to name a LAMBDA function.
Once the function gets a name, you can use it just like any other inbuilt function.
Whichever of the two formula variations you choose, from the end-user perspective, the syntax is as simple as this:
Taking the previous example a little further, let's replace not only the country abbreviations but the state abbreviations as well. For this, type the abbreviations (old values) in column D beginning in D2 and the full names (new values) in column E beginning in E2.
In B2, enter the MultiReplace function:
=MultiReplace(A2:A10, D2, E2)
Hit Enter and enjoy the results :)
How this formula works
The clue to understanding the formula is understanding recursion. This may sound complicated, but the principle is quite simple. With each iteration, a recursive function solves one small instance of a bigger problem. In our case, the MultiReplace function loops through the old and new values and, with each loop, performs one replacement:
(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))
As with nested SUBSTITUTE functions, the result of the previous SUBSTITUTE becomes the text parameter for the next SUBSTITUTE. In other words, on each subsequent call of MultiReplace, the SUBSTITUTE function processes not the original text string, but the output of the previous call.
To handle all the items on the old list, we start with the topmost cell, and use the OFFSET function to move 1 row down with each interaction:
OFFSET(old, 1, 0)
The same is done for the new list:
OFFSET(new, 1, 0)
The crucial thing is to provide a point of exit to prevent recursive calls from proceeding forever. It is done with the help of the IF function - if the old cell is empty, the function returns text it its present form and exits:
=LAMBDA(text, old, new, IF(old="", text, MultiReplace(…)))
=LAMBDA(text, old, new, IF(old<>"", MultiReplace(…), text))
In principle, the MultiReplace function discussed in the previous example can handle individual characters as well, provided that each old and new character is entered in a separate cell, exactly like the abbreviated and full names in the above screenshots.
If you'd rather input the old characters in one cell and the new characters in another cell, or type them directly in the formula, then you can create another custom function, named ReplaceChars, by using one of these formulas:
=LAMBDA(text, old_chars, new_chars, IF(old_chars<>"", ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1)), text))
=LAMBDA(text, old_chars, new_chars, IF(old_chars="", text, ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))))
Remember to name your new Lambda function in the Name Manager as usual:
And your new custom function is ready for use:
To give it a field test, let's do something that is often performed on imported data - replace smart quotes and smart apostrophes with straight quotes and straight apostrophes.
First, we input the smart quotes and smart apostrophe in D2, straight quotes and straight apostrophe in E2, separating the characters with spaces for better readability. (As we use the same delimiter in both cells, it won't have any impact on the result - Excel will just replace a space with a space.)
After that, we enter this formula in B2:
=ReplaceChars(A2:A4, D2, E2)
And get exactly the results we were looking for:
It is also possible to type the characters directly in the formula. In our case, just remember to "duplicate" the straight quotes like this:
=ReplaceChars(A2:A4, "“ ” ’", """ "" '")
How this formula works
The ReplaceChars function cycles through the old_chars and new_chars strings and makes one replacement at a time beginning from the first character on the left. This part is done by the SUBSTITUTE function:
SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars))
With each iteration, the RIGHT function strips off one character from the left of both the old_chars and new_chars strings, so that LEFT could fetch the next pair of characters for substitution:
ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))
Before each recursive call, the IF function evaluates the old_chars string. If it is not empty, the function calls itself. As soon as the last character has been replaced, the iteration process finishes, the formula returns text it its present form and exits.
Note. Because the SUBSTITUTE function used in our core formulas is case-sensitive, both Lambdas (MultiReplace and ReplaceChars) treat uppercase and lowercase letters as different characters.
In case the LAMBDA function is not available in your Excel, you can write a user-defined function for multi-replace in a traditional way using VBA.
To distinguish the UDF from the LAMBDA-defined MultiReplace function, we are going to name it differently, say MassReplace. The code of the function is as follows:
Like LAMBDA-defined functions, UDFs are workbook-wide. That means the MassReplace function will work only in the workbook in which you have inserted the code. If you are not sure how to do this correctly, please follow the steps described in How to insert VBA code in Excel.
Once the code is added to your workbook, the function will appear in the formula intellisense - only the function's name, not the arguments! Though, I believe it's no big deal to remember the syntax:
In Excel 365, due to support for dynamic arrays, this works as a normal formula, which only needs to be entered in the top cell (B2):
=MassReplace(A2:A10, D2:D4, E2:E4)
In pre-dynamic Excel, this works as an old-style CSE array formula: you select the entire source range (B2:B10), type the formula, and press the Ctrl + Shift + Enter keys simultaneously to complete it.
Advantages: a decent alternative to a custom LAMBDA function in Excel 2019, Excel 2016 and earlier versions
Drawbacks: the workbook must be saved as a macro-enabled .xlsm file
If you love automating common tasks with macros, then you can use the following VBA code to find and replace multiple values in a range.
To make use of the macro right away, you can download our sample workbook containing the code. Or you can insert the code in your own workbook.
Before running the macro, type the old and new values into two adjacent columns as shown in the image below (C2:D4).
And then, select your source data, press Alt + F8, pick the BulkReplace macro, and click Run.
As the source rage is preselected, just verify the reference, and click OK:
After that, select the replace range, and click OK:
Advantages: setup once, re-use anytime
Drawbacks: the macro needs to be run with every data change
In the very first example, I mentioned that nested SUBSTITUTE is the easiest way to replace multiple values in Excel. I admit that I was wrong. Our Ultimate Suite makes things even easier!
To do mass replace in your worksheet, head over to the Ablebits Data tab and click Substring Tools > Replace Substrings.
The Replace Substrings dialog box will appear asking you to define the Source range and Substrings range.
With the two ranges selected, click the Replace button and find the results in a new column inserted to the right of the original data. Yep, it's that easy!
Tip. Before clicking Replace, there is one important thing for you to consider - the Case-sensitive box. Be sure to select it if you wish to handle the uppercase and lowercase letters as different characters. In this example, we tick this option because we only want to replace the capitalized strings and leave the substrings like "fr", "uk", or "ak" within other words intact.
If you are curious to know what other bulk operations can be performed on strings, check out other Substring Tools included with our Ultimate Suite. Or even better, download the evaluation version below and give it a try!
That's how to find and replace multiple words and characters at once in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents