Excel, get state from City, ST Zip

dfelmey

New Member
Joined
Aug 28, 2015
Messages
29
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Something like this?
Book2
AB
1PHILADELPHIA, PA 19111PA
22) Salt Lake City, UT 84108UT
33) Magnolia, NJ 08094NJ
Sheet3
Cell Formulas
RangeFormula
B1:B3B1=MID(TRIM(A1),FIND(",",TRIM(A1))+2,2)
 
Upvote 0
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’)
 
Upvote 0
If your data is as uniform as your examples, then something like =LEFT(RIGHT(A1,8),2) should suffice.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top