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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.