Splitting addresses into four columns (in bulk)

[JodiOc]

Address	STREEET ADRESS 1	TOWN	STATE	POSTCODE
19 Stanley Street WODONGA VIC 3690
1 ABC Street ALBURY NSW 2640

<tbody>
</tbody>

Hi All

Have massive challenge & have researched, but to no avail..

The addresses are in the above format. I can separated them manually one by one, but not in bulk.

Any help would be greatly appreciated

 

[Michael M]

What about adding a helper column

Excel 2010
	A	B	C	D	E	F
1	19 Stanley Street WODONGA VIC 3690	19 Stanley Street WODONGA VIC 3690	19 Stanley Street	WODONGA	VIC	3690
Sheet5

Cell Formulas
Range	Formula
B1	=TRIM(A1)
C1	=LEFT(A1,FIND(CHAR(10),A1,1)-1)
D1	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,C1,""),E1,""),F1,""),CHAR(10),"")
E1	=TRIM(LEFT(RIGHT(SUBSTITUTE(B1," ",REPT(" ",100)),200),100))
F1	=RIGHT(B1,4)

 

[hiker95]

Michael M,

Nicely done - one for my archives, again - thanks again.

 

[JodiOc]

Hi Michael M.... I managed to make all spaces singular using the Trim() function.

 

[JodiOc]

All sorted. Thanks so much to all for your assistance.