Extract City and State from address

[scojax22]

I have a database of full addresses, including city and state in 1 cell. I want to add two new columns for city and state, and need a formula for each - I am using Left(Find",") to extract the address and Right(5) for the zip - but the city and state are giving me problems.

Any help would be great.

Ex: 101 Main Street, Cherry Hill, NJ 08510

 

[Jonmo1]

Welcome to the board

With the address in A2

Street: =LEFT(A2,FIND(",",A2)-1)
City: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",255)),255,255))
State: =MID(A2,LEN(A2)-7,2)
Zip: =RIGHT(A2,5)

 

[scojax22]

Awesome. No idea what the City formula did, but it works like a charm!

 

[Rick Rothstein]

City: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",255)),255,255))

Awesome. No idea what the City formula did, but it works like a charm!

There could be one possible problem with Jonmo1's City formula depending on what is allowed in the address part of your data. If suite or apartment numbers are allowed, they usually appear comma-separated from the address proper, something like this...

101 Main Street, Apt. 3B, Cherry Hill, NJ 08510

If your database allows that, then Jonmo1's formula will return "Apt. 3B" instead of "Cherry Hill". Here is a formula that should work with or without suite/apartment numbers...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,",",REPT(" ",250)),500),250))

 

[zsunderland]

Welcome to the board

With the address in A2

Street: =LEFT(A2,FIND(",",A2)-1)
City: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",255)),255,255))
State: =MID(A2,LEN(A2)-7,2)
Zip: =RIGHT(A2,5)

You are a god, thank you

 

[Warwick89]

There could be one possible problem with Jonmo1's City formula depending on what is allowed in the address part of your data. If suite or apartment numbers are allowed, they usually appear comma-separated from the address proper, something like this...

101 Main Street, Apt. 3B, Cherry Hill, NJ 08510

If your database allows that, then Jonmo1's formula will return "Apt. 3B" instead of "Cherry Hill". Here is a formula that should work with or without suite/apartment numbers...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,",",REPT(" ",250)),500),250))

This is on the track of what I'm looking for, but how do I make this work if my data contains a comma between the state and the postcode?

Examples:

81 Ghostgum Bvd, Banksia Grove, WA, 6031

150 Camborne Pwy, Butler, WA, 6036

Cnr Yanchep Beach Rd & Marmion Ave, Yanchep, WA, 6035

Centro Stirling, 54 Sanford St, Geraldton, WA, 6530

Lakeside Joondalup Shop. Ctr, 420 Joondalup Dr, Joondalup, WA, 6027

19 Neerabup Road, Clarkson, WA, 6030

Stirling Central Shop. Ctr, 478 Wanneroo Rd, Westminster, WA, 6061

Dog Swamp Shop. Ctr, 6 Wanneroo Rd, Yokine, WA, 6060

Glenview MarketPl, Cnr Hamelin And Bellefin Drs, Ballajura, WA, 6066

360-366 Grand Promenade, Dianella, WA, 6059

 

[Rick Rothstein]

Does ALL of your data have a comma between the state and post code (or only some of it)?

 

[kweaver]

Have you tried FLASH FILL? Not perfect but you might get close.
Is it always WA?

Book3
	A	B	C
1	81 Ghostgum Bvd, Banksia Grove, WA, 6031	Banksia Grove	WA
2	150 Camborne Pwy, Butler, WA, 6036	Butler	WA
3	Cnr Yanchep Beach Rd & Marmion Ave, Yanchep, WA, 6035	Yanchep	WA
4	Centro Stirling, 54 Sanford St, Geraldton, WA, 6530	Geraldton	WA
5	Lakeside Joondalup Shop. Ctr, 420 Joondalup Dr, Joondalup, WA, 6027	Joondalup	WA
6	19 Neerabup Road, Clarkson, WA, 6030	Clarkson	WA
7	Stirling Central Shop. Ctr, 478 Wanneroo Rd, Westminster, WA, 6061	Westminster	WA
8	Dog Swamp Shop. Ctr, 6 Wanneroo Rd, Yokine, WA, 6060	Yokine	WA
9	Glenview MarketPl, Cnr Hamelin And Bellefin Drs, Ballajura, WA, 6066	Ballajura	WA
10	360-366 Grand Promenade, Dianella, WA, 6059	Dianella	WA
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=TEXTAFTER(TEXTBEFORE(A1,", WA"),", ",-1)
C1:C10	C1	=LEFT(RIGHT(A1,8),2)

 

[kweaver]

Or maybe:

Book3
	A	B	C
1	81 Ghostgum Bvd, Banksia Grove, WA, 6031	Banksia Grove	WA
2	150 Camborne Pwy, Butler, WA, 6036	Butler	WA
3	Cnr Yanchep Beach Rd & Marmion Ave, Yanchep, WA, 6035	Yanchep	WA
4	Centro Stirling, 54 Sanford St, Geraldton, WA, 6530	Geraldton	WA
5	Lakeside Joondalup Shop. Ctr, 420 Joondalup Dr, Joondalup, WA, 6027	Joondalup	WA
6	19 Neerabup Road, Clarkson, WA, 6030	Clarkson	WA
7	Stirling Central Shop. Ctr, 478 Wanneroo Rd, Westminster, WA, 6061	Westminster	WA
8	Dog Swamp Shop. Ctr, 6 Wanneroo Rd, Yokine, WA, 6060	Yokine	WA
9	Glenview MarketPl, Cnr Hamelin And Bellefin Drs, Ballajura, WA, 6066	Ballajura	WA
10	360-366 Grand Promenade, Dianella, WA, 6059	Dianella	WA
11	111 Main Street, Newark, NJ, 12345	Newark	NJ
Sheet1

Cell Formulas
Range	Formula
B1:B11	B1	=TEXTAFTER(TEXTBEFORE(A1,", ",-2),", ",-1)
C1:C11	C1	=LEFT(TEXTAFTER(A1,", ",-2),2)

[XD]=TEXTAFTER(TEXTBEFORE(A1,", ",-2),", ",-1)[/XD][/XR][/RANGE]

 

[kvsrinivasamurthy]

TRY. A2:A8 data.
Formula in B2.

=TEXTSPLIT(TEXTJOIN(",",0,BYROW(A2:A8,LAMBDA(ro,LET(a,TEXTSPLIT(ro,","),b,TEXTSPLIT(INDEX(a,3)," "),INDEX(a,2)&"_"&INDEX(b,2))))),"_",",")