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.
Find and replace multiple values with nested SUBSTITUTE
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
Search and replace multiple entries with XLOOKUP
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
Multiple replace using recursive LAMBDA function
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
Example 1. Search and replace multiple words / strings at once
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))
Or
=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:
Where:
- Text - the source data
- Old - the values to find
- New - the values to replace with
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:
MultiReplace(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(…)))
or
=LAMBDA(text, old, new, IF(old<>"", MultiReplace(…), text))
Example 2. Replace multiple characters in Excel
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))
Or
=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:
Where:
- Text - the original strings
- Old - the characters to search for
- New - the characters to replace with
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.
Mass find and replace with UDF
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:
Where:
- Input_range - the source range where you want to replace values.
- Find_range - the characters, strings, or words to search for.
- Replace_range - the characters, strings, or words to replace with.
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
Bulk replace in Excel with VBA macro
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.
How to use the macro
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:
Done!
Advantages: setup once, re-use anytime
Drawbacks: the macro needs to be run with every data change
Multiple find and replace in Excel with Substring tool
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!
Available downloads
Multiple find and replace in Excel (.xlsm file)
Ultimate Suite 14-day fully-functional version (.exe file)
37 comments
I've been using the "Mass find and replace with UDF" VBA code, but would like to not just search for specific strings of text, but whole words only. Example: if I seek to replace all instances of "art" in a cell to "picture" I don't want it to change "This art is of a cart" to "This picture is of a cpicture" I want "This picture is of a cart"
I am having a hard time figuring out a way to edit this code to get this result though. I found some other codes that use word barriers "\b" but I can't figure out how to incorporate them.
We cannot help you with this. We do not do VBA code creation or customization on request.
Just replace " art " for " picture " (note the blank spaces).
The macro perfectly worked! But is there a way to apply it across multiple sheets?
Hello! If I understand your task correctly, our Advanced Find and Replace tool may help you solve it in a few clicks. You can use it to search through multiple spreadsheets and workbooks, and easily select or replace all the values found. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Text: I eat apples, bananas, carrots and cucumbers
Text to remove
A6 - apples
A7 - bananas
Text to replace
B6 - cookies
B7 - Chocolate
Formula: =REPLACETEXTS(A2,$A$6:$A$7,$B$6:$B$7)
Revised Text: I eat cookies, Chocolate, carrots and cucumbers
The above formula is not working so looking for a defined function to replace multiple texts using a simple looking formula without nesting multiple SUBSTITUTE functions. couldn't find correct formula anywhere. Can you please help me with this
Hi! I think you can choose one of the methods described in the article above to replace multiple texts.
Non of them worked:(
A nested SUBSTITUTE function will work fine.
Hey,
Is there a way to replace and add
Like; i want to replace S with Sweater and add a value in different row
Example sweater = 70 , pants = 20
Then when i type S in a1 it replace with sweater and b1 it automatically places 70 and in a5 i type p it replaces with pants and in b5 it replaces with 20
Hi! To replace a letter with a word, you can try AutoCorrect.
To get a number based on different values in another cell, you can use a nested IF or a VLOOKUP formula instead of multiple IF.
Hi All,
What if I have a sheet and I want to replace a words with "empty" without save the changes to a new line, instead just replace the words with empty without send the new data to a new line.
I hope it is clear.
Any one have any idea?
time="00:00:38" action="blocked" catdesc="Internet Telephony"
time="00:00:35" action="passthrough" catdesc="Outlook"
time="00:00:31" action="passthrough" catdesc="Information Technology"
time="00:00:28" action="passthrough" catdesc="Outlook"
time="00:00:28" action="passthrough" catdesc="Outlook"
time="00:00:26" action="passthrough" catdesc="Information Technology"
time="00:00:26" action="passthrough" catdesc="Outlook"
time="00:00:23" action="passthrough" catdesc="Web-based Applications"
time="00:00:22" action="passthrough" catdesc="Outlook"
time="00:00:21" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:21" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:21" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:21" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:21" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:21" action="passthrough" catdesc="Outlook"
time="00:00:21" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:21" action="passthrough" catdesc="Content Servers"
time="00:00:21" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:20" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:20" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:20" action="passthrough" catdesc="Search Engines and Portals"
time="00:00:19" action="passthrough" catdesc="Outlook"
time="00:00:19" action="passthrough" catdesc="Search Engines and Portals"
like here, I want to replace word time=", ", action=",..etc with empty, without save the data to a new lines.
Hi! To replace the text in the current cell, use these guidelines: How to use Find and Replace in Excel most efficiently. I recommend paying attention to the Substring tool. Using it, you can quickly replace, remove, or extract multiple values. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
No need to use VBA anymore for this! Super simple formula now!
=REDUCE(A1,$b$2:$b$6,LAMBDA(a,b,SUBSTITUTE(a,b,OFFSET(b,0,1))))
Where A1 is the target cell to replace text
b2:b6 is where the keywords are stored (texts to be replaced)
c2:c6 (referenced by the offset) is where the replacing texts are stored
Credit: Chandoo
This formula worked extremely well for me and I am definitely using this instead of MultiReplace. I like that I can see all elements of the formula in my cell and not have to use the Name Manager; also that I don't have to name the whole range of cells I want to use the replace formula on. When I did try using MultiReplace, I kept getting a Name error. This is really straight forward, although you do have to have the replacement values in the column directly to the right of the texts to be replaced.
thanks heaps, it's great formula - love it!
The popup that comes up at the bottom of the screen wont close when the close button, and the movement makes reading the content of the page unreadable.
Hi Floyd,
Thank you for your feedback and sorry for the inconvenience. Our tech engineer has emailed you regarding the issue. Thank you for your time.
I was faced with this issue today. Here's another solution that uses REDUCE:
/**
* Case sensitive substitute takes a list of old and new values and performs mulitple replacements
text, oldvalues, and newvalues can be arrays.
*/
SubstituteMultple = LAMBDA(text, oldvalues, newvalues,
REDUCE(text,
oldvalues,
LAMBDA(a,b,
SUBSTITUTE(a, b, XLOOKUP( b, oldvalues, newvalues,"" ))
)
)
)
The MultiReplace LAMDA function is genius!! I had built up a large number of nested SUBSTITUTE functions and needed to add many more. It was becoming very messy, but the MultiReplace function solved all of that. Thanks!
Copy pasted the "=LAMBDA(text,old,new, IF(old"", MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))" Function. Hit ctrl+f3, created a new name, pasted it into the "refers to" field.
Made an example cell with the text "ääää". made two new cells, "ä" and "a". wrote "=MultiReplace(A1, A2, A3)" into it, where A1 is "ääää", A2 is "ä" and A3 is "a". But the cell where i pasted the formula just says "#NAME!" :(
i dont get what i did wrong, this would be a huge help for me as i need to replace special characters like äöüéá etc. in both cases for a huge range of values, and have to use like 20 nested SUBSTITUTES right now :D
Nevermind, im a morron, my excel is not in english so i had to translate the IF and OFFSET first...
Hi, Thanks a lot for this post! :D
I have a doubt how would I use the macro and make it replace only if it finds the entire string.
For Example
Old id New ID
123 A
1234 B
In this case, I get 1234 replaced as A4 instead of B.
Any tips or suggestions?. Thanks
Hi!
According to your explanation, it is very difficult to understand what you want to replace and what result you want to get.
Hi! Sorry for not being clear. I want to replace 1234 with B. When I run the macro "1234" gets replaced as "A"4. Since Old ID of "A" is "123".
Source range:
123
1234
Replace range:
Old ID New ID
123 A
1234 B
Result:
A
A4
Expected result:
A
B
Hope I made it a bit more clear. :)
Hello!
I can't guess which macro or formula you are using. Replace "1234" first and then replace "123". For example, use the SUBSTITUTE function.
=SUBSTITUTE(SUBSTITUTE(A1,"1234","B"),"123","A")
I hope it’ll be helpful.
Hello, Thanks for the suggestion. I got the same idea :D I was using the bulk replace macro.
I was wondering if there was a way to be specific. So that I can run the macro once.
But thanks again for your response.
I have the source data scattered on the worksheet and the source data consists of 4 cells, for example, one of the cells has the number 6 in it, the next has 100, the next has 171, and the next has 1700
the range has 2 columns Old and New
A1 is 6
B1 is 6
A2 is 100
B2 is 100
A3 is 171
B3 is 9001
A4 is 1700
B4 is 91101
so in the source when it finds the number 171 in the cell it should change it to 1700...
instead i get 95960759903001
Hi!
I don't really understand what you want to do. The number in a cell cannot be changed using an Excel formula. If you need to return a number, then why should 1700 be returned if 171 is found? And if 6 is found, what should be returned?
Great post!! Thank you!!
Thanks for this! Not only was I able to use one of these solutions to solve my needs in the workbook, I learned stuff, too! MOST excellent!
Thank you for your valuable and knowledgeable advice
The XLOOKUP technique worked flawlessly for my data
8-)
The Bulk replace macro is AMAZING. Thanks a lot!
The macro looks like the perfect solution, but... when I run it nothing happens?!?
This applies in the downloaded workbook or if I insert the macro in my own workbook?
Any suggestions or advice would be very welcome!
Hi!
Detailed instructions on how to insert a macro into an Excel workbook, you can also read on our blog.
The BulkReplace DVA is is a fantastic tool! The only issue i'm having is that the text it's stringing together uses a comma when i really need it to separate values by semicolon OR Return. Because the new data already has a comma in it. Could you let me know how i can change the code to use semicolons? Any help is appreciated! thanks
Hello!
I'm happy to hear you like our custom function :) But I do not exactly understand the issue. In the code of the BulkReplace macro, there are no "hardcoded" delimiters. It just uses the pairs of old/new values that you provide for the replace range.
For us to be able to help you better, please specify your source strings, replace range values, and the expected result.