Exceljet

Quick, clean, and to the point

Find and replace multiple values

Excel formula: Find and replace multiple values
Generic formula 
=SUBSTITUTE(A1,INDEX(find,B$1),INDEX(replace,B$1))
Explanation 

To find and replace multiple values with a formula, you can set up one table with one replacement per column, and feed in find/replace pairs from another table. In the example shown, G5 contains this formula:

=SUBSTITUTE(B5,INDEX(find,C$4),INDEX(replace,C$4))

where "find" is the named range I5:I9, and "replace" is the named range J5:J10.

This is a "concept" formula to show how you can run a series of find and replace operations sequentially on text, with a way to easily add more replacements. In addition, the replacements are "dynamic" – when you change values in "find" or "replace" ranges, formulas update immediately.

How this formula works

The values in the range I5:I9 hold values to find and replace, with one pair per row. These are fed into formulas in the main table to the left to run sequential replacements, one per column in the columns named 1-5, with the SUBSTITUTE function.

The actual replacements are done with the SUBSTITUTE function like this:

=SUBSTITUTE(text,findtext,replacetext)

where text is the incoming value, findtext is the text to look for, and replacetext is the text to replace with.

The in column C, the text comes from the "input" column, but the reference to B5 is relative, so the column changes as the formula is copied to the right.

=SUBSTITUTE(B5,INDEX(find,C$4),INDEX(replace,C$4))

Essentially, each subsequent SUBSTITUTE begins with the result from the previous SUBSTITUTE.

The find text is provided by INDEX like this:

INDEX(find,G$4)

where "find" is the named range I5:I9. Note we using the table header row to get the correct row number.

The replace text is provided by INDEX in the same way:

INDEX(replace,C$4)

where "replace" is the named range J5:J10.

In the range C5:G5 therefore, the formulas resolve to:

=SUBSTITUTE(B5,"red","pink")
=SUBSTITUTE(C5,"blue","green")
=SUBSTITUTE(D5,"gold","silver")
=SUBSTITUTE(E5,"white","black")
=SUBSTITUTE(f5,"-"," ")

More columns can be added to the table to handle more find/replace pairs. Each time a column is added, a new row needs to be added to "find" and "replace" ranges.

Note: It is also possible to "nest" SUBSTITUTE function inside one another to do the same kind of thing, but the formula can become complex and unwieldy.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.