Excel 2007 : Extract CITY in ADDRESS string

**kwj1966** · 02-03-2012, 03:45 PM

I have a list of about 8000 addresses. Unfortunately, the address field included the street, city, state and zip. The state and zip were relatively easy to extract since they were set character lengths (zipcode was 5 character and state was 2). Now what I'm left with is a string that includes the address and city.

Lastly, to extract the city, I took the remaining address data and ran a "text-to-columns". As a result, I was able to extract most of the cities, but not all of them. And now this is my dilemma.

Unfortunately, there is no space between the "last character of the address" and the "city" for almost 4000 of the records, therefore I have character strings similar to the following:

1234 Your Street Ste 104Houston
5678 My Street Building 2San Diego
91011 Our Street NWNew York

With 4000 records, it would be quite a manual exercise to extract the cities from the addresses. Does anyone have any idea of how to extract the cities from these character strings?

ONE IDEA: Many of the cities have numerical characters next to them as in the first two examples above. Is there a way to search a text string to find the rightside characters UP TO a numerical character. This would at least get me much further along. Any ideas?

**Haseeb Avarakkan** · 02-03-2012, 04:18 PM

Originally Posted by kwj1966

...Is there a way to search a text string to find the rightside characters UP TO a numerical character...

This will remove from starting to last numeric.

=REPLACE(A1,1,MATCH(10,INDEX(--MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),0)),"")

**NBVC** · 02-03-2012, 04:19 PM

Assuming city names are no longer than 20 chars... then try this to extract from the ones that have the number as you describe in 1st 2....

Assuming first string in A2:

In B2: =RIGHT(RIGHT(A2,200),20-MIN(FIND(0,SUBSTITUTE(RIGHT(A2,20),{1,2,3,4,5,6,7,8,9},0)&0))+1) copied down

In C2: =SUBSTITUTE(B2,LOOKUP(9.999999999E+307,--("0"&MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0 123456789")),ROW($1:$30)))),"") copied down to get city, you can then copy and paste special >> Values and fill in the blanks...

**daddylonglegs** · 02-03-2012, 04:20 PM

Do you have a list of cities? If so then with data in A2 down try this formula in B2 down

=RIGHT(A2,MATCH(2^15,INDEX(MATCH(RIGHT(A2,ROW(INDIRECT("1:20"))),CityList,0),0)))

That will find the first match in the CityList looking at the last 20 characters of A2, then the last 19, last 18 etc. all the way down