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

 

[Fluff]

Another option

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

Cell Formulas
Range	Formula
B2:C12	B2	=CHOOSECOLS(TRIM(TEXTSPLIT(A2,",")),-3,-2)
Dynamic array formulas.

 

[Warwick89]

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]

Many thanks @kweaver

I have used your formula from B1 and extrapolated into street field 2 and state field... your formula from C2 didn't work where my state codes were 3 letters.

Summer Reset Program - Gloves.xlsx
	G	H	I	J	K	L
2	81 Ghostgum Bvd, Banksia Grove, WA, 6031	81 Ghostgum Bvd		Banksia Grove	WA	6031
3	150 Camborne Pwy, Butler, WA, 6036	150 Camborne Pwy		Butler	WA	6036
4	Cnr Yanchep Beach Rd & Marmion Ave, Yanchep, WA, 6035	Cnr Yanchep Beach Rd & Marmion Ave		Yanchep	WA	6035
5	Centro Stirling, 54 Sanford St, Geraldton, WA, 6530	Centro Stirling	54 Sanford St	Geraldton	WA	6530
6	Lakeside Joondalup Shop. Ctr, 420 Joondalup Dr, Joondalup, WA, 6027	Lakeside Joondalup Shop. Ctr	420 Joondalup Dr	Joondalup	WA	6027
48	36-38 Hartley St, Alice Springs, NT, 870	36-38 Hartley St		Alice Springs	NT	870
49	247 Trower Rd, Casuarina, NT, 810	247 Trower Rd		Casuarina	NT	810
50	Tenants PO Box 80, Gateway Shopping Centre, 1 Roystonea Avenue Yarrawonga NT 0832, Palmerston, NT, 830	Tenants PO Box 80	1 Roystonea Avenue Yarrawonga NT 0832	Palmerston	NT	830
51	Newton Village Shop. Ctr, 299 Montacute Rd, Newton, SA, 5074	Newton Village Shop. Ctr	299 Montacute Rd	Newton	SA	5074
52	27 Liverpool St, Port Lincoln, SA, 5606	27 Liverpool St		Port Lincoln	SA	5606
53	976 North East Rd, Modbury, SA, 5092	976 North East Rd		Modbury	SA	5092
54	Renmark Shop. Ctr, Renmark Ave, Renmark, SA, 5341	Renmark Shop. Ctr	Renmark Ave	Renmark	SA	5341
55	Centre Plaza, Cnr Fifteenth St And Deakin Ave, Mildura, VIC, 3500	Centre Plaza	Cnr Fifteenth St And Deakin Ave	Mildura	VIC	3500
56	297 Peachey Rd, Munno Para West, SA, 5115	297 Peachey Rd		Munno Para West	SA	5115
57	1 Forster St, Kadina, SA, 5554	1 Forster St		Kadina	SA	5554
58	382-396 Watterloo Crn Rd, Burton, SA, 5110	382-396 Watterloo Crn Rd		Burton	SA	5110
59	Northpark Shop. Ctr, 264 Main North Rd, Prospect, SA, 5082	Northpark Shop. Ctr	264 Main North Rd	Prospect	SA	5082
60	11 Interchange Place, Rochedale, QLD, 4123	11 Interchange Place		Rochedale	QLD	4123
61	Stocklands Shoppingtown, 537 Mulgrave Rd, Earlville, QLD, 4870	Stocklands Shoppingtown	537 Mulgrave Rd	Earlville	QLD	4870
62	Cnr Alfred Street and Koch Street, Manunda, QLD, 4870	Cnr Alfred Street and Koch Street		Manunda	QLD	4870
63	63 Front St, Mossman, QLD, 4873	63 Front St		Mossman	QLD	4873
Sheet1

Cell Formulas
Range	Formula
H2:H6,H48:H63	H2	=LEFT(G2,FIND(",",G2)-1)
I2:I6,I48:I63	I2	=TEXTAFTER(TEXTBEFORE(G2,", ",-3,,,""),", ",-1,,,"")
J2:J6,J48:J63	J2	=TEXTAFTER(TEXTBEFORE(G2,", ",-2),", ",-1)
K2:K6,K48:K63	K2	=TEXTAFTER(TEXTBEFORE(G2,", ",-1),", ",-1)
L2:L6,L48:L63	L2	=RIGHT(G2,4)

 

[Warwick89]

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

Thanks @Rick Rothstein yes, the comma was between all the states and post codes... and @kweaver helped me find the solution I was looking for.

 

[Peter_SSs]

@Warwick89
Could you manually complete the expected results (all columns) for the sample data in post 12 (or at least enough of the rows to give an example of all the variations possible with your data) and post that with XL2BB?

 

[kweaver]

Many thanks @kweaver

I have used your formula from B1 and extrapolated into street field 2 and state field... your formula from C2 didn't work where my state codes were 3 letters.

Summer Reset Program - Gloves.xlsx
	G	H	I	J	K	L
2	81 Ghostgum Bvd, Banksia Grove, WA, 6031	81 Ghostgum Bvd		Banksia Grove	WA	6031
3	150 Camborne Pwy, Butler, WA, 6036	150 Camborne Pwy		Butler	WA	6036
4	Cnr Yanchep Beach Rd & Marmion Ave, Yanchep, WA, 6035	Cnr Yanchep Beach Rd & Marmion Ave		Yanchep	WA	6035
5	Centro Stirling, 54 Sanford St, Geraldton, WA, 6530	Centro Stirling	54 Sanford St	Geraldton	WA	6530
6	Lakeside Joondalup Shop. Ctr, 420 Joondalup Dr, Joondalup, WA, 6027	Lakeside Joondalup Shop. Ctr	420 Joondalup Dr	Joondalup	WA	6027
48	36-38 Hartley St, Alice Springs, NT, 870	36-38 Hartley St		Alice Springs	NT	870
49	247 Trower Rd, Casuarina, NT, 810	247 Trower Rd		Casuarina	NT	810
50	Tenants PO Box 80, Gateway Shopping Centre, 1 Roystonea Avenue Yarrawonga NT 0832, Palmerston, NT, 830	Tenants PO Box 80	1 Roystonea Avenue Yarrawonga NT 0832	Palmerston	NT	830
51	Newton Village Shop. Ctr, 299 Montacute Rd, Newton, SA, 5074	Newton Village Shop. Ctr	299 Montacute Rd	Newton	SA	5074
52	27 Liverpool St, Port Lincoln, SA, 5606	27 Liverpool St		Port Lincoln	SA	5606
53	976 North East Rd, Modbury, SA, 5092	976 North East Rd		Modbury	SA	5092
54	Renmark Shop. Ctr, Renmark Ave, Renmark, SA, 5341	Renmark Shop. Ctr	Renmark Ave	Renmark	SA	5341
55	Centre Plaza, Cnr Fifteenth St And Deakin Ave, Mildura, VIC, 3500	Centre Plaza	Cnr Fifteenth St And Deakin Ave	Mildura	VIC	3500
56	297 Peachey Rd, Munno Para West, SA, 5115	297 Peachey Rd		Munno Para West	SA	5115
57	1 Forster St, Kadina, SA, 5554	1 Forster St		Kadina	SA	5554
58	382-396 Watterloo Crn Rd, Burton, SA, 5110	382-396 Watterloo Crn Rd		Burton	SA	5110
59	Northpark Shop. Ctr, 264 Main North Rd, Prospect, SA, 5082	Northpark Shop. Ctr	264 Main North Rd	Prospect	SA	5082
60	11 Interchange Place, Rochedale, QLD, 4123	11 Interchange Place		Rochedale	QLD	4123
61	Stocklands Shoppingtown, 537 Mulgrave Rd, Earlville, QLD, 4870	Stocklands Shoppingtown	537 Mulgrave Rd	Earlville	QLD	4870
62	Cnr Alfred Street and Koch Street, Manunda, QLD, 4870	Cnr Alfred Street and Koch Street		Manunda	QLD	4870
63	63 Front St, Mossman, QLD, 4873	63 Front St		Mossman	QLD	4873
Sheet1

Cell Formulas
Range	Formula
H2:H6,H48:H63	H2	=LEFT(G2,FIND(",",G2)-1)
I2:I6,I48:I63	I2	=TEXTAFTER(TEXTBEFORE(G2,", ",-3,,,""),", ",-1,,,"")
J2:J6,J48:J63	J2	=TEXTAFTER(TEXTBEFORE(G2,", ",-2),", ",-1)
K2:K6,K48:K63	K2	=TEXTAFTER(TEXTBEFORE(G2,", ",-1),", ",-1)
L2:L6,L48:L63	L2	=RIGHT(G2,4)

Suppose you change the second formula to: =TEXTBEFORE(TEXTAFTER(A1:A11,", ",-2),",")

Book1
	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, NNJ, 12345	Newark	NNJ
Sheet1

Cell Formulas
Range	Formula
B1:B11	B1	=TEXTAFTER(TEXTBEFORE(A1:A11,", ",-2),", ",-1)
C1:C11	C1	=TEXTBEFORE(TEXTAFTER(A1:A11,", ",-2),",")
Dynamic array formulas.

 

[Rick Rothstein]

Here is a single formula (placed in cell B1) which will automatically fill Columns B and C with the desired data...

=TEXTSPLIT(TEXTAFTER(A1:A11,", ",{-3,-2}),", ")