Multiple Find and Replace to replace a list of strings

I've been scouring the internet trying to figure out how to do this via a macro, add-in, or separate program. I think I've been close, but no cigar. I'm hoping one of you excel pros will be able to help. Here is my situation.

I'm combining 15 phone directories that overlap in some places. I'm removing the duplicates with various find and replace methods, but I have hit a road block. The road block is with city abbreviations and city misspellings. I have over 500 cities with cities spelled and abbreviated 25+ ways.

I was able to compile a list of misspellings and abbreviations. (some by hand and some by automation from prior data)

I'm working with two spreadsheets.

(SpreadSheet1)

- my main spreadsheet with all the phone directories combined.
- the combined directory with Name, Address, City, State, Phone, etc.
- city field is littered with errors

(SpreadSheet2)

- I sorted the cities.
- Column A has the correct spelling of the city name.
- Column B thru Column (XXX) has the variations.

What I would like to do is some sort of "FIND AND REPLACE" in SpreadSheet1 using SpreadSheet2.ColB thru SpreadSheet2.Col(XXX) as the FIND And SpreadSheet2.ColA as the REPLACE.

Also, the Find and Replace has to be based on the "entire contents of the cell" otherwise some of the partial city names will be replaced.

If anyone can give me any insight on what to do I would be forever in your debt.

Thanks,

WalterP34



P.S. If you're having trouble visualizing my question here is an example

Chicago might be spelled in the city field in Spreadsheet1 the following ways.

Chi
Chcg
Chicag
Chic
Chcgo

I need to do a batch replace of all of those.

So that is why I created SpreadSheet2. like this.

Chicago, Chi, Chcg, Chicag, Chic, Chcgo

-Thanks

I attached an example of my raw data and what I would need the finish product to look like using Chicago, Boston, and New York.

Worksheet 1 has the unsorted mistakes

Worksheet 2 has the sorted mistakes to replace

Worksheet 3 is an example of what the finished product needs to look like.

-Thanks

Thanks so much!!

I am trying to do something similar.
On the left column (refer to tab 'Sample') I would like to detect a word in the cell (refer to 'Equivalents' tab, columns 'Category (old)','Subcategory (old)') and when the words are detected I want the equivalents to be returned (refer to columns 'Category (new)','Subcategory (new)').
Please advise.