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!

 

[sandy666]

maybe try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]src[/td][td=bgcolor:#70AD47]Text Between Delimiters[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]843 E Main Ste 203, Medford, Oregon 97504-7137[/td][td=bgcolor:#E2EFDA]Oregon[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7648 Victory Blvd, Newport, Arkansas 72112[/td][td]Arkansas[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Administration Bldg 200; 1509 N Boulder Ave, Russellville, Arkansas 72801-2222[/td][td=bgcolor:#E2EFDA]Arkansas[/td][/tr]
[/table]

[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]

 

[teston]

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!

 

[sandy666]

Right, Mac Office doesn't support PowerQuery (still!!!)

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

Maybe

have a nice day

 

[haylau]

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

 

[sandy666]

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)

Me?! Are you sure?!

 

[Fluff]

How about

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)

 

[Scott Huish]

If you want a formula:
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),510),255))

Excel 2010
	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
Sheet1

Cell Formulas
Range	Formula
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))

 

[James Lever]

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.
[TABLE="width: 600"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Addr[/TD]
[TD]Street[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD]7648 Victory Blvd, Newport, Arkansas 72112[/TD]
[TD]7648 Victory Blvd[/TD]
[TD]Newport[/TD]
[TD]Arkansas [/TD]
[TD]72112
[/TD]
[/TR]
</tbody>[/TABLE]

 

[teston]

If you want a formula:
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),510),255))

Worked like a charm! Thanks Scott and everyone else for your help!