How to use mid or left functions to seperate city,st,zip with no commas

[sinaranje]

Hi all,



I have a list of approx. 2000 address that are in the following format

|Address 2|

Los Angeles CA 90210
Miami FL 20200
Bethesda MD 20817

My desired format:

City| State | Zip

Los Angeles CA 90210

All of these addresses have no commas to appropriately separate the city from the state or zip. Therefore using a mid function to pull out CITY, STATE, ZIP is difficult.

Does anyone have some ideas on how to segregate them? Thanks.

[excelledsoftware]

Assuming that all of your addresses have 5 digit zipcodes and 2 character states you can use a combination of mid, right, and len formulas to do this quite easily.

The following formulas start with addresses in cell A2

For the City: =MID(A2,1,LEN(A2)-9)
For the State: =MID(A2,LEN(A2)-7,2)
For the Zip: =RIGHT(A2,5)

Let me know if that works out.

Thanks

[excelledsoftware]

Here is your attachment with the formulas filled in. Some of your addresses have an extended zip code. Just filter these and run the formula in red font for these addresses.

[macropod]

For the City: =LEFT(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789"))-5)
For the State: =MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789"))-3,2)
For the Zip: =RIGHT(C2,LEN(C2)+1-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789")))

[excelledsoftware]

Originally Posted by macropod

For the City: =LEFT(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789"))-5)
For the State: =MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789"))-3,2)
For the Zip: =RIGHT(C2,LEN(C2)+1-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789")))

Wow Macropod, I never knew you could use curly braces in a search formula like that. I learned something really important today. Thanks.