Need formula help to separate address from one cell to multiple cells

[steve5740]

Address Examples:
GREENBRIAR MALL 1712 RING RD CHESAPEAKE VA 23320
101 N. HIGHWAY 274 HWY 175 @ HWY 274 KEMP TX 75143-0000
229 NORTH SEVEN POINTS RT. #8 - BOX 925 SEVEN POINTS TX 75143-0000
3223 LEMMON AVE #100 DALLAS TX 75204
8001 WESTERN HILLS BLVD FORT WORTH TX 76108

Hello -

I have a spreadsheet with addresses in one cell/column, and need to separate the data by address, city, state and zip code. Because the data is different lengths, including 2 word cities, it has been difficult to develop a successful formula and text to column does not help. Any suggestions would be helpful.

I have tried the following formulas and it does work except for 2 word cities. How do I adjust the formula?

Cell B2 =TRIM(SUBSTITUTE(A2,D2&" "&E2,""))
Cell C2 =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2," "&E2,"")," ",REPT(" ",50)),50))
Cell D2 =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),99))

 

[Eric W]

Parsing out addresses is one of the toughest things to do, just because there are so many exceptions. I found a set of formulas that works for this test set. In order to handle 2-word cities, I had to create a table of city names with more than 1 word. Whenever you find such a city in your data, you'll need to add it to the table. You should be able to see how to adapt that. You do need to put the xxxxx in any unused fields in the city table.

Excel 2012
	A	B	C	D	E	F	G
1	GREENBRIAR MALL 1712 RING RD CHESAPEAKE VA 23320	GREENBRIAR MALL 1712 RING RD	CHESAPEAKE	VA	23320		SEVEN POINTS
2	101 N. HIGHWAY 274 HWY 175 @ HWY 274 KEMP TX 75143-0000	101 N. HIGHWAY 274 HWY 175 @ HWY 274	KEMP	TX	75143-0000		FORT WORTH
3	229 NORTH SEVEN POINTS RT. #8 - BOX 925 SEVEN POINTS TX 75143-0000	229 NORTH SEVEN POINTS RT. #8 - BOX 925	SEVEN POINTS	TX	75143-0000		xxxxx
4	3223 LEMMON AVE #100 DALLAS TX 75204	3223 LEMMON AVE #100	DALLAS	TX	75204		xxxxx
5	8001 WESTERN HILLS BLVD FORT WORTH TX 76108	8001 WESTERN HILLS BLVD	FORT WORTH	TX	76108		xxxxx
6							xxxxx
7							xxxxx
8							xxxxx
9							xxxxx
10							xxxxx
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(A1,LEN(A1)-LEN(E1)-LEN(D1)-LEN(C1)-3))
D1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(E1)-1)," ",REPT(" ",255)),255))
E1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
C1	{=IFERROR(INDEX($G$1:$G$10,MAX(IF(ISNUMBER(FIND($G$1:$G$10,A1)),ROW($G$1:$G$10),-1))),TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(E1)-LEN(D1)-2)," ",REPT(" ",255)),255)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Let me know if this helps.

 

[steve5740]

Thank you Eric.

It worked except for some minor manual adjustments I had to make. Perhaps there is a fix for these and I will send examples asap.

Thank you!

 

[Eric W]

Glad to help!