Excel: find and replace multiple values at once

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.

• Nested SUBSTITUTE formula to replace multiple values
• XLOOKUP formula to search for multiple words
• Multi-replace using recursive LAMBDA function
• Mass find and replace with UDF
• Bulk-replace macro to find and replace multiple values
• Multiple find and replace using Substring tools

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:

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:

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 :)

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:

ReplaceChars function" title="Name a custom ReplaceChars 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:

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)

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.

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.

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)

The above is the detailed content of Excel: find and replace multiple values at once. For more information, please follow other related articles on the PHP Chinese website!