Extract State name from Address

[teston]

Hello,

I have a bunch of addresses where I need to extract state name to a new cell.

The addresses look the following way:

[TABLE="width: 347"]
<colgroup><col></colgroup><tbody>[TR]
[TD]843 E Main Ste 203, Medford, Oregon 97504-7137[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 347"]
<colgroup><col></colgroup><tbody>[TR]
[TD]7648 Victory Blvd, Newport, Arkansas 72112[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 347"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Administration Bldg 200; 1509 N Boulder Ave, Russellville, Arkansas 72801-2222[/TD]
[/TR]
</tbody>[/TABLE]

I have used Rick Rothstein's solution from another thread to extract cities and it works flawlessly even with additions to address like "Administration Bldg 200;".

However, how do I extract state names? The only pattern I see here is that state name always comes after last comma and before the zip code.

Thanks!

 

[jtakw]

Hi,

Since there are State names that contain more than a Single word, this will handle them also:

Book1
	A	B
1	843 E Main Ste 203, Medford, Oregon 97504-7137	Oregon
2	7648 Victory Blvd, Newport, Arkansas 72112	Arkansas
3	Administration Bldg 200; 1509 N Boulder Ave, Russellville, Arkansas 72801-2222	Arkansas
4	7648 Victory Blvd, New York, New York 72112	New York
5	843 E Main Ste 203, Medford, North Carolina 97504-7137	North Carolina
Sheet544

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,", ",REPT(" ",99),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))),99)),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),""))

 

[teston]

Hi,

Since there are State names that contain more than a Single word, this will handle them also:

Works perfectly now! Thanks!

 

[jtakw]

You're welcome, welcome to the forum, and thanks for the feedback.