Splitting addresses into four columns (in bulk)

[JodiOc]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Address
[/TD]
[TD]STREEET ADRESS 1
[/TD]
[TD]TOWN
[/TD]
[TD]STATE
[/TD]
[TD]POSTCODE
[/TD]
[/TR]
[TR]
[TD]19 Stanley Street
WODONGA VIC 3690
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 ABC Street
ALBURY NSW 2640
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.