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.

Attached Files

example for forum 1.xlsx (11.4 KB, 11 views)

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
Let me know if that works out.

Thanks

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.

Attached Files

example for forum 1.xlsx (14.0 KB, 11 views)

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")))

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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