Extract State Abbreviation with No Commas - HELP!

[drockdamian]

Hello All,

I need help extracting the state abbreviation and zip code from this single string address line. There are two space in between the state abbreviation and the zip code; only one space between the end of zip code and country. Here is a sample.

8270 Petaluma Hill Rd Attn: Broc Croll Penngrove CA 94951-9747 USA

1105 Arden Rd Pasadena CA 91106-4005 USA

2010 Wentworth Dr Billings MT 59105-3535 USA

3372 S 7780 W Magna UT 84044-2041 USA

 

[drockdamian]

You're welcome. Thanks for the follow-up.
.. and your kind words. All the best to you too.

How can we extract the City in the above scenario?

 

[Scott Huish]

The problem with cities has always been that they can be more than one word which is what makes it hard to differentiate with what comes before it.