Parsing City, State, Zip from one cell

Artemis · Nov 21, 2011

I am having trouble figuring out how to parse city, state, and zipcode from one cell. I uploaded a list 18,000 cities, and there are a couple problems with the list. It has no common delimiter, nor a common length state name (like a two letter abbreviation).

Examples of the addresses are:

Stony Creek, New York 12878-1613
Dulles, Virginia 20166-6923
Sturgeon Bay, Wisconsin 54235-8308
Florence, Arizona 85132
...

(They are all in this format, but some zip codes are only five digits)

I am very unfamiliar with basic, so functions would be preferred. But any help is very much appreciated.

Thanks.

sheetspread · Nov 21, 2011

If the city is listed first and followed by a comma, you can split it there.
The zip code can be extracted by using right() and an if to detect any dash.