Fixing Name problem

Hi,
I am facing an issue with fixing names in Excel using a formula. The problem is as follows:

In my role as Product Analyst, I have to source data from various data sources and after applying ETL using 'Analytical Canvas', export the dat to an EXcel to generate reports. Now the issue is that in different databases, the same name has been spelled differently and captured. I need to bring it to correct spelling to uniformity and use the data to generate reports. For example, say place like 'Bombay' has in some cases been input as 'Mumbai' or 'Mum bai', or 'Bom' only. I need to standardise the spelling as 'BOMBAY" to be able to generate the report. Is there any formula in EXcel, other than the 'filter' with 'Find and Replace' command, to perform this data cleansing?? Apperciate help.. Regards

If possible I would create a lookup tabl eof the missplet words and what they should be returned as

iferror(vlookup(a1, lookupRangeForYouToDefine, 2, false),a1)

something along the above lines

Thanks davsth. Can you please simulate with the place name problem I have mentioned in my thread and if possible, let me know the formula worked out

If you had a table of all the possible spellings, along with what you want to appear instead, then it would simplify this task. It would also help if you attached a sample Excel workbook, and you can do this by following the instructions in the yellow banner at the top of the screen.

Pete

I am in agreement with Pete. Post a small sample as per the instructions in the yellow banner. that way the answer you will get will more closely meet your needs. It may also throw up some other problems that you have not mentioned (trailing or beginning spaces for example)