Convert similar strings into a common string

**booo** · 11-03-2010, 02:43 PM

I am pretty sure that there is an easy way to do this. But I am not able to figure it out. I have a column which list names of companies but in different ways. For Eg)

Huber Sunher
Huber + Sunher
HuberSunher
Huber Sunher Vermont

I want to find out a way which would help me display just "Huber & Sunher" for all these data.

Kindly let me know how this is done.

Thanks,
Booo

**Leith Ross** · 11-03-2010, 02:51 PM

Hello boo,

It would help to see all the data. In cases like this, a pattern needs to exist to parse and recombine the strings. If you have a workbook, please post it. This will save a lot of Q and A, and get you results much faster.

**booo** · 11-03-2010, 03:09 PM

Hi,

Thanks for responding. I have attached a sample spreadsheet. I cannot add a lot of detailed data because the data I work is from my employer.

If u have a look at the spreadsheet, you will be able to see that the Name's have been entered in different combination. That data is being imported from SAP and since they do not have an automated system to choose the name, people enter it in their own ways.

If there was a way to make the names to display in one form depending upon their starting strings, that would be great.

Thanks,
booo

**Leith Ross** · 11-03-2010, 03:54 PM

Hello booo,

If these entries are free form then you can only do so much with code due to the number of possible variations. There needs to be something distinct and consistent. For example, using capital letters to start a word. Using this method spaces between words can be ignored, like DiyDiy. Without the capital letters, one or spaces would need to be used to separate the words.

Cases like "Huber + Suhner Vermont" pose another problem. Is "Vermont" a parent company, a state, or something else? How many other similar cases are there like this one?

If you were working with these variations only:
Huber Suhner
HuberSuhner
huber suhner
Huber and Suhner
Huber+ Suhner

It would be easy to determine where the breaks between words are. Otherwise, it would be quite a task to check for and parse the names. You would need a heuristic database to automate the process reliably.