Find and replace multiple values

There is no built-in function to perform a series of find and replace operations in Excel, but this article explains how you can create a formula that does the same thing. The goal in this example is to replace multiple values with multiple corresponding values in a "batch replace all" operation. The worksheet shows a list of text strings in column B (Input), a list of results in column D (Output), and a table with "find" and "replace" values in columns F and G. For each text string in column B, we want to find and replace each value in column F with the corresponding value in column G. For example, every instance of "red" should be replaced with "ruby", every instance of "blue" should be replaced with "teal", and so on.

The formula shown above features the REDUCE function, one of Excel's newer dynamic array functions. If you are using an older version of Excel that doesn't include the REDUCE function, you can achieve the same result by nesting multiple SUBSTITUTE functions together in a more complicated formula, as explained below.

Table of contents

• Naming variables with the LET function
• Creating an array of indices
• Using REDUCE with lifting
• Inside the LAMBDA function
• Cleaning up the result
• More substitutions and dynamic ranges
• Using REGEXREPLACE for word boundaries
• Solution for Legacy Excel

Naming variables with the LET function

The formula uses the LET function to organize variables and make the formula easier to read and maintain. The first three variables define the input ranges:

=LET(
    range,B5:B16,
    find,F5:F9,
    replace,G5:G9,

The variable range contains the text strings to process, find contains the values to search for, and replace contains the corresponding replacement values. With these variables in place, we can refer to these ranges by name in the formula, which makes the logic easier to follow.

Creating an array of indices

The next variable uses the SEQUENCE function to create a sequence of numbers that corresponds to the number of "find/replace" pairs:

ix,SEQUENCE(ROWS(find)),

Since there are 5 rows in the range F5:F9 (red, blue, green, purple, orange), the ROWS function returns 5, and SEQUENCE returns the array {1;2;3;4;5}. This array serves two purposes: it gives us a list of indices that we can use with the INDEX function to retrieve corresponding "find" and "replace" values, and it gives us an array that REDUCE can iterate over.

Using REDUCE with lifting

The core of the formula is the REDUCE function, which applies a series of substitutions to each text string:

result,REDUCE(range,ix,LAMBDA(a,i,
    SUBSTITUTE(a,INDEX(find,i),INDEX(replace,i))
)),

This is a clever use of REDUCE that takes advantage of a built-in process called "lifting". The key to understanding this formula is to look at what is provided as the initial_value and what is provided as the array. Normally, you might expect the values to process (B5:B16) to be provided as the array argument. However, in this formula, the values to process are provided as the initial_value, and the array of indices (ix) is provided as the array argument. This setup means we are actually calling the REDUCE function 12 times (once for each value in the range B5:B16). Then, for each value in B5:B16, REDUCE iterates over the array of indices and applies the custom LAMBDA function.

Inside the LAMBDA function

The LAMBDA function looks like this:

LAMBDA(a,i,SUBSTITUTE(a,INDEX(find,i),INDEX(replace,i)))

The a argument is the accumulator, which holds the current value from the range B5:B16. The i argument is the current index from the array {1;2;3;4;5}. Inside the LAMBDA, the SUBSTITUTE function performs the actual replacement. The INDEX function retrieves the "find" value and the "replace" value using the current index i:

INDEX(find,i)    // gets the value to find
INDEX(replace,i) // gets the replacement value

For example, when i is 1, INDEX retrieves "red" from the find range and "ruby" from the replace range. SUBSTITUTE then replaces all instances of "red" with "ruby" in the current text string. The result from SUBSTITUTE becomes the new accumulator value, which is used as the starting point for the next iteration. This process continues for all indices (1 through 5), applying each substitution in sequence. When all substitutions have been completed for a given text string, REDUCE moves on to the next text string in the range B5:B16.

Cleaning up the result

The final step wraps the result from REDUCE in the TRIM function:

TRIM(result)

The TRIM function removes any extra spaces from the text strings. This is a safety measure that cleans up the output in case any of the substitutions create extra whitespace.

More substitutions and dynamic ranges

You can easily extend this formula to handle more find/replace pairs by adding more rows to the table in columns F and G. Then, update the ranges in the formula as needed to include the new rows. The rest of the formula will automatically adjust because it uses the ROWS function to determine how many pairs to process. If you want dynamic ranges that expand to include new find/replace rows automatically, you can use an Excel Table or use the new TRIMRANGE function (or the related dot operator).

Using REGEXREPLACE for word boundaries

If you need better control over word boundaries (i.e., you don't want to match "red" inside "bored"), you can replace the SUBSTITUTE function with the REGEXREPLACE function like this:

=LET(
    range,B5:B16,
    find,F5:F9,
    replace,G5:G9,
    ix,SEQUENCE(ROWS(find)),
    result,REDUCE(range,ix,LAMBDA(a,i,
        REGEXREPLACE(a,"\b"&INDEX(find,i)&"\b",INDEX(replace,i))
    )),
    TRIM(result)
)

The \b is a regex word boundary marker that ensures matches only occur at word boundaries. If you are new to Regular Expressions (regex) in Excel, see our regex guide here.

Solution for Legacy Excel

If you are using an older version of Excel that doesn't include the REDUCE function, you can achieve the same result by nesting multiple SUBSTITUTE functions together. However, this approach is less flexible and more difficult to maintain than the REDUCE-based solution above. With nested SUBSTITUTE functions, you need to manually add or remove function calls each time you change the number of find/replace pairs, and the formula quickly becomes difficult to read. The formula used in the worksheet below looks like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4)),INDEX(find,5),INDEX(replace,5))

The formula uses the SUBSTITUTE function to perform each substitution, feeding in find/replace pairs from a table using the INDEX function. The basic pattern looks like this:

=SUBSTITUTE(text,find,replace)

where "text" is the incoming value, "find" is the text to look for, and "replace" is the text to replace with. To perform multiple substitutions, you nest SUBSTITUTE functions together, with each subsequent SUBSTITUTE beginning with the result from the previous SUBSTITUTE, where "find" is a named range containing the values to find (F5:F9), and "replace" is a named range containing the replacement values (G5:G9). The INDEX function retrieves both the "find" text and the "replace" text like this:

INDEX(find,1) // first "find" value
INDEX(replace,1) // first "replace" value

So, to run the first substitution (look for "red", and replace it with "pink"), we use:

=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))

The INDEX function makes this solution "dynamic" – if any of the values in the find/replace table are changed, results update immediately.

Line breaks for readability

You'll notice this kind of nested formula is quite difficult to read. By adding line breaks, you can make the formula much easier to read and maintain:

=
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
B5,
INDEX(find,1),INDEX(replace,1)),
INDEX(find,2),INDEX(replace,2)),
INDEX(find,3),INDEX(replace,3)),
INDEX(find,4),INDEX(replace,4)),
INDEX(find,5),INDEX(replace,5))

The formula bar in Excel ignores extra white space and line breaks, so the above formula can be pasted in directly.

To expand and collapse the formula bar quickly, use the keyboard shortcut Control + Shift + U

More substitutions

More rows can be added to the table to handle more find/replace pairs. Each time a pair is added, the formula needs to be updated to include the new pair. It's also important to make sure the named ranges (if you are using them) are updated to include new values as needed. Alternately, you could use a proper Excel Table for dynamic ranges, instead of named ranges.

Other uses

The same approach can be used to clean up text by "stripping" punctuation and other symbols from text with a series of substitutions. For example, the formula on this page shows how to clean and reformat telephone numbers.