Extract State name from Address

teston

New Member
Joined
Feb 5, 2019
Messages
6
Hello,

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

The addresses look the following way:

843 E Main Ste 203, Medford, Oregon 97504-7137

<colgroup><col></colgroup><tbody>
</tbody>
7648 Victory Blvd, Newport, Arkansas 72112

<colgroup><col></colgroup><tbody>
</tbody>
Administration Bldg 200; 1509 N Boulder Ave, Russellville, Arkansas 72801-2222

<colgroup><col></colgroup><tbody>
</tbody>

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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
maybe try PowerQuery (Get&Transform)

srcText Between Delimiters
843 E Main Ste 203, Medford, Oregon 97504-7137Oregon
7648 Victory Blvd, Newport, Arkansas 72112Arkansas
Administration Bldg 200; 1509 N Boulder Ave, Russellville, Arkansas 72801-2222Arkansas

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],
    #"Inserted Text Between Delimiters" = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([src], ", ", " ", {0, RelativePosition.FromEnd}, 0), type text)
in
    #"Inserted Text Between Delimiters"[/SIZE]
 
Upvote 0
maybe try PowerQuery (Get&Transform)

thanks sandy666,

looks like a great solution! However, I'm using Excel for Mac, and PowerQuery doesn't seem to work here :(

Maybe there's a way to achieve same result with formula or VBA?

Thanks!
 
Upvote 0
I wouldset about this using working columns first to split down the address
You said on the other thread that you have managed to extract some data (Left(Find",") to extract the address and Right(5) for the zip)

So by breaking into columns you started with
843 E Main Ste 203, Medford, Oregon 97504-7137
using left to extract address then in the working column you would have
Medford, Oregon 97504-7137
Then using right and a working column you would have
Medford, Oregon
Use left again to get rid of Medford and that leaves you with the state
 
Upvote 0
How about
Code:
Function teston(Cl As Range) As String
   Dim sp As Variant
   sp = Split(Cl)
   teston = sp(UBound(sp) - 1)
End Function
Used like =teston(A1)
 
Upvote 0
If you want a formula:
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),510),255))


Excel 2010
AB
1843 E Main Ste 203, Medford, Oregon 97504-7137Oregon
27648 Victory Blvd, Newport, Arkansas 72112Arkansas
3Administration Bldg 200; 1509 N Boulder Ave, Russellville, Arkansas 72801-2222Arkansas
Sheet1
Cell Formulas
RangeFormula
B1=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),510),255))
B2=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),510),255))
B3=TRIM(LEFT(RIGHT(SUBSTITUTE(A3," ",REPT(" ",255)),510),255))
 
Upvote 0
Try this: It relies on, there is always a Street, City, State, separated by a comma (extra commas will skew it) with a space before the Zip.

Street Formula: =TRIM(MID(SUBSTITUTE($A3,",",REPT(" ",LEN($A3))),((1-1)*LEN($A3))+1,LEN($A3)))
City: = TRIM(MID(SUBSTITUTE($A3,",",REPT(" ",LEN($A3))),((2-1)*LEN($A3))+1,LEN($A3)))
State:=LEFT(TRIM(MID(SUBSTITUTE($A3,",",REPT(" ",LEN($A3))),((3-1)*LEN($A3))+1,LEN($A3))),IFERROR(FIND(" ",TRIM(MID(SUBSTITUTE($A3,",",REPT(" ",LEN($A3))),((3-1)*LEN($A3))+1,LEN($A3)))),20))
Zip:=MID(TRIM(MID(SUBSTITUTE($A3,",",REPT(" ",LEN($A3))),((3-1)*LEN($A3))+1,LEN($A3))),FIND(" ",TRIM(MID(SUBSTITUTE($A3,",",REPT(" ",LEN($A3))),((3-1)*LEN($A3))+1,LEN($A3))))+1,10)

See below results: Kudos to fellow member addepallibabu.
AddrStreetCityStateZip
7648 Victory Blvd, Newport, Arkansas 721127648 Victory BlvdNewportArkansas 72112

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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