Text to Column

[noob]

A1 contains a full address but they are not constant where i can use text to column by spaces. Is there another way to do this in excel2010. I'm looking to get STREET# IN COLUMN A, DIRECTION STREET AND SUFFIX IN B, CITY IN C, STATE IN D AND ZIP IN COLUMN E

examples


51 W MAPLE CT DECATUR ILLINOIS 62526
2255 SANDS DR DECATUR ILLINOIS 62526
60 GLENVIEW DR DECATUR ILLINOIS 62521
7 W HIGDON DR DECATUR ILLINOIS 62521
45 W REEDER DR DECATUR ILLINOIS 62521
3487 NANCY RD DECATUR ILLINOIS 62521
1715 W DANCE LAND RD DECATUR ILLINOIS 62521
132 POINT BLUFF DR DECATUR ILLINOIS 62521
2699 W HARRY LAND RD DECATUR ILLINOIS 62521
2644 FORREST GREEN DR DECATUR ILLINOIS 62521

 

[ukmikeb]

Hi

Try this -

Excel Workbook
	A	B	C	D	E	F	M
1	51	W MAPLE CT	DECATUR	ILLINOIS	62526		51 W MAPLE CT DECATUR ILLINOIS 62526
2	2255	SANDS DR	DECATUR	ILLINOIS	62526		2255 SANDS DR DECATUR ILLINOIS 62526
3	60	GLENVIEW DR	DECATUR	ILLINOIS	62521		60 GLENVIEW DR DECATUR ILLINOIS 62521
4	7	W HIGDON DR	DECATUR	ILLINOIS	62521		7 W HIGDON DR DECATUR ILLINOIS 62521
5	45	W REEDER DR	DECATUR	ILLINOIS	62521		45 W REEDER DR DECATUR ILLINOIS 62521
6	3487	NANCY RD	DECATUR	ILLINOIS	62521		3487 NANCY RD DECATUR ILLINOIS 62521
7	1715	W DANCE LAND RD	DECATUR	ILLINOIS	62521		1715 W DANCE LAND RD DECATUR ILLINOIS 62521
8	132	POINT BLUFF DR	DECATUR	ILLINOIS	62521		132 POINT BLUFF DR DECATUR ILLINOIS 62521
9	2699	W HARRY LAND RD	DECATUR	ILLINOIS	62521		2699 W HARRY LAND RD DECATUR ILLINOIS 62521
10	2644	FORREST GREEN DR	DECATUR	ILLINOIS	62521		2644 FORREST GREEN DR DECATUR ILLINOIS 62521
Sheet18

Excel 2007

Cell Formulas
Range	Formula
A1	=LEFT($M1,FIND(" ",$M1)-1)
B1	=MID($M1,FIND(" ",$M1)+1,FIND("@",SUBSTITUTE($M1," ","@",LEN($M1)-LEN(SUBSTITUTE($M1," ",""))-2))-FIND(" ",$M1))
C1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($M1," ",REPT(" ",19),LEN($M1)-LEN(SUBSTITUTE($M1," ",""))-1)," ",REPT(" ",19),LEN($M1)-LEN(SUBSTITUTE($M1," ",""))-2),25,25))
D1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($M1," ",REPT(" ",19),LEN($M1)-LEN(SUBSTITUTE($M1," ","")))," ",REPT(" ",19),LEN($M1)-LEN(SUBSTITUTE($M1," ",""))-1),38,25))
E1	=RIGHT($M1,5)

I have moved your source addresses across to Column M because otherwise you destroy your source data.

Also, the solution will NOT cope with a city which has a space eg Des Moines.

hth

 

[Peter_SSs]

Also, the solution will NOT cope with a city which has a space eg Des Moines.

.. or a State with a space eg South Dakota

noob, this type of question is notoriously difficult because of the great variety of address forms that can exist.

 

[ukmikeb]

.. or a State with a space eg South Dakota

Thanks Peter, should have got that too.

Had a similar problem a few weeks ago and suggested to the then OP that the exceptions were probably few and far between and could be spotted visually and then amended accordingly.

 

[noob]

Thank you very much gentleman did it semi mannualy last night using text to column, filter, and replace. It will be good to have it for the next time. I did test it and it took alot less time

 

[noob]

One way to fix the citys and states with spaces would be to replace them i.e St Paul replaced with StPaul..... I think

 

[Peter_SSs]

One way to fix the citys and states with spaces would be ro replace them i.e St Paul replaced to StPaul..... I think

To do that, wouldn't you need a complete list of two-worded cities (and states)?

Even then you may still need to be careful as you may have an address like
153 St Paul Dr Maryville ..
That is, the St Paul in this case is not the city name.

Then you also have cities with more than two words (eg Boca del Mar, FL)

As Mike mentioned earlier, formulas will be some help to get the bulk of it done, but you really need a human eye to check over the results and probably tweak some of them manually.