Pull State from Non-Uniformly Formatted List of Addresses

[SirGruffles]

Hello!

I have a massive list of addresses, and I'm looking to pull the state (and the state alone) out of it, and into a separate column.

Here's a few rows as an example (not real addresses, to protect identities):

2000 ARLINGTON OAKS DR S; MOBILE Alabama 36695-8739

978 VIRGINIA ST SABETHA Kansas 66534-2431

5200 WILSHIRE RD CHESAPEAKE Virginia 23321-3280

<tbody>
</tbody>

Not all rows have the 9 digit zip-code, m, but every state is fully spelled out.

Is there a formula I could use to pull out the state?

Thank you for your help!

SirGruffles

 

[Joe4]

The problem is that many states have two names, like "New York, North Carolina, West Virginia, South Carolina, etc".
Is your data really structured like that, where the state is in Proper Case, and all the other words are in Upper Case?
If so, we may be able to create a User Defined Function in VBA to pull out the state name based on that.

 

[jtakw]

Hi,

This is sort of a "workaround"...

First, build a list of all 50 States.
The following formula starts looking for the states from the list After the 3rd space in the Address string, since there's a possibility that a Street Name can match a State Name, as in your 2nd sample:

Book1
	A	B	C	D
1	2000 ARLINGTON OAKS DR S; MOBILE Alabama 36695-8739	Alabama		Alabama
2	978 VIRGINIA ST SABETHA Kansas 66534-2431	Kansas		California
3	5200 WILSHIRE RD CHESAPEAKE Virginia 23321-3280	Virginia		Arizona
4				Kansas
5				Oregon
6				Virginia
7				New Mexico
Sheet198

Cell Formulas
Range	Formula
B1	=LOOKUP(2,1/SEARCH(D$1:D$7,MID(A1,FIND("|",SUBSTITUTE(A1," ","|",3)),255)),D$1:D$7)

Formula copied down.

I didn't build the full State list, but you can take it from here.