Extract State Abbreviation from an Address

[MacIndy]

Sample Address:

Dale Cleaners 976 Old Henderson Rd Columbus OH 43220 USA I have over 20,000 of these that I need to extract the state from. What's the best way to do this?

 

[Joe4]

If it always follows that format of ending with State + Zip + Country and there are no other extra spaces, then try this:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),300),100))

for an entry in cell A1.