Help with formula - pull state abbreviation out of string of text

[algioia]

If I have this string of data, what is a formula I can use to just pull the state abbreviation out of the string? Or is there anything you could suggest?

--Circuit ID #266..Z Loc: 111 N. Post Oak LN, Houston, TX 77024
--Circuit ID #791..5805 Chimney Rock Rd.Houston, Texas 77081

Considering there is more than one comma in these, I cannot do the basic search formula that I would use.
The 2nd example has the state spelled out. Maybe we can back into it from the zip code?

[algioia]

I did create this lookup table. The StateAbb column would be what I'd want returned, if the criteria of State, Zip is found. This is the comma and space after the city name, the state abbreviation, space and first digit of zip code.

ST, ZIP StateAbb
, AL 3 AL
, AR 7 AR
, AZ 8 AZ
, CA 9 CA
, CO 8 CO
, CT 06 CT
, DC 2 DC
, GA 3 GA
, LA 7 LA
, MA 0 MA
, MD 2 MD
, MI 4 MI
, MS 3 MS
, NC 2 NC
, ND 5 ND
, NV 8 NV
, NY 1 NY
, OH 4 OH
, OK 7 OK
, PA 1 PA
, SC 2 SC
, TN 3 TN
, TX 7 TX
, VA 2 VA
, WV 2 WV
, WY 8 WY

[Fluff13]

This will get you the state

Please Login or Register  to view this content.

[algioia]

Thank you!

[Fluff13]

You're welcome