Find and replace multiple values

To find and replace multiple values with a formula, you can nest multiple SUBSTITUTE functions together, and feed in find/replace pairs from another table using the INDEX function. In the example shown, we are performing 4 separate find and replace operations. The formula in G5 is: =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)) where "find" is the named range E5:E8, and "replace" is the named range F5:F8. See below for info on how to make this formula easier to read.

April 14, 2025 · 1 min · 103 words · John Hill

This makes the solution “dynamic” if any of these values are changed, results update immediately.

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.

Line breaks added in formula bar for readability and maintenance

Alternately, you could use aproper Excel Tablefor dynamic ranges, instead of named ranges.

For example, the formula on this page shows how toclean and reformat telephone numbers.

you’re able to use INDEX to retrieve individual values, or entire rows and columns.

Line breaks added in formula bar for readability and maintenance

The MATCH function is often used together with INDEX to provide row and column numbers….

Line breaks added in formula bar for readability and maintenance

Excel formula: Clean and reformat telephone numbers

Excel formula: Strip non-numeric characters

Excel formula: Strip numeric characters from cell

Excel SUBSTITUTE function

Excel INDEX function

More substitutions#

More substitutions