Similar texts with minor differences - How to get just ONE (a standard one)

[heytoluca]

Hello,


I have a very large database that contains names with minor differences on column A such as:
A
EXCEL AMERICAN COMPANY S.A.
EXCEL AMERICAN COMPANY SA.
EXCEL AMERICAN COMPANY, S.A.
ASSOCIATION, INDUSTRIAL
ASSOCIATION INDUSTRIAL
L.A. COMPANY CO
LA COMPANY CO


As you can see, this makes a mess when dealing with numbers associated with them and makes analysis more difficult.
What I've been trying to figure out is how to quickly get in column B a standard name for each one, for example:
B
EXCEL AMERICAN COMPANY S.A.
ASSOCIATION INDUSTRIAL
L.A. COMPANY CO


I was thinking of using a combination of the FIND and EXTRACT formula or maybe LEFT as well. (Maybe VB?)
Please use your excel wizardry to help a fellow comrade!


David

[NBVC]

Please post links to all threads in other forums you have posted this same question.

[heytoluca]

Please post links to all threads in other forums you have posted this same question.

Here they are, so far, only a MrExcel user has posted a creative solution but still we cant seem to find a fit-all formula to standarize the database:

MREXCEL /forum/excel-questions/780217-similar-texts-minor-differences-how-get-just-one-standard-one.html#post3819929
OZGRID /forum/showthread.php?t=188620
EXCELKEY /forum/viewtopic.php?f=2&t=5889

[Rizky]

Maybe easier if you build lookup table for reference to change the list to standard list, have take a look the file attach..

Thanks
Rizky

[heytoluca]

Maybe easier if you build lookup table for reference to change the list to standard list, have take a look the file attach..

Thanks
Rizky

Wow now that is some wizardry, at least for me, it looks like the it could be my one solution, could you PLEASE explain the formula for me? I want to learn!

Thanks a lot!
David