Extract Address and City from String

[vraman0513]

Hello - I am trying to extract the address and city from the following addresses. Could anyone help?

[TABLE="width: 425"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2718 WEST BUCKNER ROAD DALLAS, TX 75237[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

are you just dropping the TX 75237 ?

 

[oldbrewer]

[TABLE="width: 334"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2718 WEST BUCKNER ROAD DALLAS, TX 75237[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2718 WEST BUCKNER ROAD DALLAS[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]=LEFT(A1,SEARCH(",",A1)-1)[/TD]
[/TR]
</tbody>[/TABLE]

 

[vraman0513]

I can already extract the zip and state to seperate cells....just need to extract the address and city seperately.

 

[vraman0513]

Thanks -- however, is there a way to just extract the city and the address seperately and not together in one cell?

 

[admiral100]

Hi,

try this one :

=RIGHT(SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND(",",A1)+1),""),LEN(SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND(",",A1)+1),""))-FIND("*",SUBSTITUTE(SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND(",",A1)+1),"")," ","*",LEN(SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND(",",A1)+1),""))-LEN(SUBSTITUTE(SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND(",",A1)+1),"")," ","")))))

 

[vraman0513]

Thank you!!

How about for something like this without a comma seperating the city and state?

[TABLE="width: 444"]
<tbody>[TR]
[TD="class: xl64, width: 444"]456 N LAWNDALE AVE MOUNTAIN VIEW CA 94043-1716[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Thank you!!

How about for something like this without a comma seperating the city and state?

[TABLE="width: 444"]
<tbody>[TR]
[TD="class: xl64, width: 444"]456 N LAWNDALE AVE MOUNTAIN VIEW CA 94043-1716[/TD]
[/TR]
</tbody>[/TABLE]

Actually, the missing comma is the least of your troubles with that one... put the comma in and tell me how you know that city consists of two name as compared to your first example (DALLAS) which only has one word? And if you think you solve that one, does you solution handle cities with three name like SALT LAKE CITY, UTAH

 

[vraman0513]

Correct - doesn't work for cities with multiple names, this is one of the additional challenges I was facing.

 

[Rick Rothstein]

Correct - doesn't work for cities with multiple names, this is one of the additional challenges I was facing.

The only possible way around this dilemma that I can think of is for you to create a list of all possible street designations (Road, Street, Lane, Circle, etc., etc.) and all of their possible abbreviations (for example, Ave, Av, Ave., Av. for Avenue) and then locate whichever one there is within the text as the break point between the address and the city. But you cannot look for the first one of these that match, rather, you need to find the last one of them (otherwise you will get fooled by STREET ROAD which is a major street name in Philadelphia, Pennsylvania). If you construct that list, we can probably drum up a solution of some kind for you.