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.
- 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
- 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.
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.
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