Parse City State Zip

[queenbean84]

Some nitwit gave me a list that looks like this:

Rochester NY 14617-2346
Buffalo NY 14216-1530
Johnson City NY 13790-2504
Amherst NY 14228

Sometimes the city name is two names and sometimes the zip codes are not +4.

I need a formula that will parse city, state, and zip.

We are using Excel 2010.

[Kyle123]

You need a list of states that are 2 letters, you can then use this to split the string.

[63falcondude]

With your strings starting in A2, try these:

D2 =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
C2 =RIGHT(SUBSTITUTE(A2," "&D2,""),2)
B2 =LEFT(A2,LEN(A2)-LEN(C2&D2)-2)

[queenbean84]

With your strings starting in A2, try these:

D2 =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
C2 =RIGHT(SUBSTITUTE(A2," "&D2,""),2)
B2 =LEFT(A2,LEN(A2)-LEN(C2&D2)-2)

Thanks! That worked! The state ended up being "Y" but since it's all NY, I can easily change it.

[63falcondude]

That's odd. Is there more than one space between the state and zip?

Either way, if you're happy with the solution provided, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.