Pull State from Non-Uniformly Formatted List of Addresses

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
26
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):
[TABLE="width: 439"]
<tbody>[TR]
[TD]2000 ARLINGTON OAKS DR S; MOBILE Alabama 36695-8739[/TD]
[/TR]
[TR]
[TD]978 VIRGINIA ST SABETHA Kansas 66534-2431[/TD]
[/TR]
[TR]
[TD]5200 WILSHIRE RD CHESAPEAKE Virginia 23321-3280[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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
ABCD
12000 ARLINGTON OAKS DR S; MOBILE Alabama 36695-8739AlabamaAlabama
2978 VIRGINIA ST SABETHA Kansas 66534-2431KansasCalifornia
35200 WILSHIRE RD CHESAPEAKE Virginia 23321-3280VirginiaArizona
4Kansas
5Oregon
6Virginia
7New Mexico
Sheet198
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top