find and replace multiple values

You can use nested SUBSTITUTE formulas in Excel to find and replace multiple values in Excel. In this example, I'll show how to replace the names of each of the 50 states with their abbreviations. You need to have one SUBSTITUTE command for each of the terms.

1. Begin by setting up the formula in a spare worksheet. Enter the formula:

SUBSTITUTE(

. . . on 50 successive lines

2. Then enter the cell with the first value you need to replace ("A2") followed by INDEX commands to refer to the terms that you're looking for:

INDEX(find,1

. . . pulled down until you get to INDEX(find,50

3. Then enter a second INDEX command referring to the terms you want to use as replacements:

),INDEX(replace,1

. . . to ),INDEX(replace,50 in the column to the right alongside the INDEX(find entries.

4. In a third column enter:

)),

. . . another 49 times with the last entry as: ))

You should have the text for the formula set up this way:

5. Next in Word or a text editor find and replace all of the paragraph breaks and tabs with nothing.

6. Copy the resulting formula back into Excel in the cell next to the first one with a state name that you want to look up the abbreviation for, and then put an equal sign in front.

7. Next set up the named ranges referred to in the formula - 'find' and 'replace'. Simply list the state names in one column. Select all of the cells and in the name box at the top left name this range, 'find'. Then do the same for the cell range with the state abbreviations, giving them the name 'replace'.

8. The formula will then generate the correct replacement for each value in column A. Just pull it down by selecting the empty cells in column B and pressing ALT + D.