PDA

View Full Version : Solved: Extract Multiple State or cities names - excel function



vishwakarma
11-16-2010, 10:23 PM
ok guys...

here is what I want.

Is there any way by which I can extract multiple names of the states from this list of companies names in separate columns with the help of excel function.

for e.g. United Way of Selma & Dallas County :- Result 1 - Selma, Result 2 - Dallas



Thanks...

macropod
11-16-2010, 10:32 PM
Hi Manoj,

Unless you have a separate list of all possible cities, similar to your States list, I think you'll find your data too unstructured for reliable extraction. Even then, given that some cities have the same names as some States (eg Washington), I think you'll still have problems.

Bob Phillips
11-17-2010, 01:11 AM
It's even worse, the first one has no state, it has a city, Dallas, but what the heck is Selma, a county? The number of possible options seems ridiculously high.

Tinbendr
11-18-2010, 10:16 PM
I don't have a solution either, but I did put together a userform that might help you select the words you need. It's not perfect, but you should get the general idea. (Hope you don't have thousands of lines. :eek: )

David

macropod
11-19-2010, 12:10 AM
It's even worse, the first one has no state, it has a city, Dallas, but what the heck is Selma, a county? The number of possible options seems ridiculously high.
I suppose if one had a comprehensive schedule of states, counties & cities, a macro could be written to test for all possible matches and return the one that achieves the greatest overall sub-string match length, but it'd be a challenge.

Even then, since "United Way of Selma & Dallas County", actually refers to 'Selma and Dallas County', Alabama, 'Selma & Dallas' would never be matched (unless one also added code to the macro to replace '&' in a test string with 'and'). Plus, where matches on names like this are made, logic suggests the 'Selma and Dallas' substring should be output to a single cell, not split between two cells with Selma in one and Dallas in the other.

vishwakarma
11-23-2010, 03:16 AM
First of all very sorry for the late reply...

Secondly, Thank you guys for all your replies...Tinbendr (http://www.vbaexpress.com/forum/member.php?u=2549)'s code will work for me.

Also, I will keep in mind in future to send the data in proper format and will be more precise to what I need.

Thanks...