Excel, get state from City, ST Zip

[dfelmey]

Sorry, I dont know how I cant figure this out. I need to get only the two digit state abbreviation from City, ST Zip. Need to make sure if the city has more than one name it pulls the state correctly. Thank you.

Here are a few examples:

1) PHILADELPHIA, PA 19111
2) Salt Lake City, UT 84108
3) Magnolia, NJ 08094

The Result I need from each:
1) PA
2) UT
3) NJ

 

[rlv01]

Something like this?

Book2
	A	B
1	PHILADELPHIA, PA 19111	PA
2	2) Salt Lake City, UT 84108	UT
3	3) Magnolia, NJ 08094	NJ
Sheet3

Cell Formulas
Range	Formula
B1:B3	B1	=MID(TRIM(A1),FIND(",",TRIM(A1))+2,2)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[RoryA]

If your data is as uniform as your examples, then something like =LEFT(RIGHT(A1,8),2) should suffice.

 

[dfelmey]

Thank you @rlv01. Worked great.

 

[rlv01]