How to find and replace multiple values at once in Excel (bulk replace)

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.

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(text, old_text1, new_text1), old_text2, new_text2), old_text3, new_text3)

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:
Find and replace multiple values with nested SUBSTITUTE in Excel 365

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.
SUBSTITUTE formula for multiple find and replace in Excel 2019 - 2007

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:
XLOOKUP formula to search and replace multiple words

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)
IFNA VLOOKUP formula for mass search and replace

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.
Name a custom MultiReplace 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:

MultiReplace(text, old, new)

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 :)
LAMBDA function to search and replace multiple words at once

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:
Name a custom <em>ReplaceChars</em> function

And your new custom function is ready for use:

ReplaceChars(text, old_chars, new_chars)

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:
LAMBDA function to replace multiple characters

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:

Function MassReplace(InputRng As Range, FindRng As Range, ReplaceRng As Range) As Variant() Dim arRes() As Variant 'array to store the results Dim arSearchReplace(), sTmp As String 'array where to store the find/replace pairs, temporary string Dim iFindCurRow, cntFindRows As Long 'index of the current row of the SearchReplace array, count of rows Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long 'index of the current row in the source range, index of the current column in the source range, count of rows, count of columns cntInputRows = InputRng.Rows.Count cntInputCols = InputRng.Columns.Count cntFindRows = FindRng.Rows.Count ReDim arRes(1 To cntInputRows, 1 To cntInputCols) ReDim arSearchReplace(1 To cntFindRows, 1 To 2) 'preparing the array of find/replace pairs For iFindCurRow = 1 To cntFindRows arSearchReplace(iFindCurRow, 1) = FindRng.Cells(iFindCurRow, 1).Value arSearchReplace(iFindCurRow, 2) = ReplaceRng.Cells(iFindCurRow, 1).Value Next 'Searching and replacing in the source range For iInputCurRow = 1 To cntInputRows For iInputCurCol = 1 To cntInputCols sTmp = InputRng.Cells(iInputCurRow, iInputCurCol).Value 'Replacing all find/replace pairs in each cell For iFindCurRow = 1 To cntFindRows sTmp = Replace(sTmp, arSearchReplace(iFindCurRow, 1), arSearchReplace(iFindCurRow, 2)) Next arRes(iInputCurRow, iInputCurCol) = sTmp Next Next MassReplace = arRes End Function

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:

MassReplace(input_range, find_range, replace_range)

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)
MassReplace user-defined function in Excel 365

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.
MassReplace user-defined function in Excel 2019

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.

Sub BulkReplace() Dim Rng As Range, SourceRng As Range, ReplaceRng As Range On Error Resume Next Set SourceRng = Application.InputBox("Source data:", "Bulk Replace", Application.Selection.Address, Type:=8) Err.Clear If Not SourceRng Is Nothing Then Set ReplaceRng = Application.InputBox("Replace range:", "Bulk Replace", Type:=8) Err.Clear If Not ReplaceRng Is Nothing Then Application.ScreenUpdating = False For Each Rng In ReplaceRng.Columns(1).Cells SourceRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value Next Application.ScreenUpdating = True End If End If End Sub

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.
Excel macro find and replace multiple values

As the source rage is preselected, just verify the reference, and click OK:
Select the source range.

After that, select the replace range, and click OK:
Select the replace range.

Done!
Multiple replace done with VBA macro

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.
Replace Substrings in Excel

The Replace Substrings dialog box will appear asking you to define the Source range and Substrings range.
Define the Source and Substrings ranges.

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!
Results of the multiple replace

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)

Table of contents

36 comments

  1. 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.

    • Just replace " art " for " picture " (note the blank spaces).

  2. The macro perfectly worked! But is there a way to apply it across multiple sheets?

  3. 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

  4. 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

  5. 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.

  6. 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.

  7. 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.

  8. 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,"" ))
    )
    )
    )

  9. 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!

  10. 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...

  11. 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! 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, 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.

  12. 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?

  13. Great post!! Thank you!!

  14. 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!

  15. Thank you for your valuable and knowledgeable advice

    The XLOOKUP technique worked flawlessly for my data

    8-)

  16. The Bulk replace macro is AMAZING. Thanks a lot!

  17. 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!

  18. 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.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)