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]

Is
19 Stanley Street
WODONGA VIC 3690
in one cell sperated by ALT + Enter ??

 

[JodiOc]

Yes that is right Michael M

 

[JodiOc]

It is exported from another database program. I am unable to change this format in the original program.

 

[Michael M]

Try this

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

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

 

[István Hirsch]

If you want a Text to Columns solution without formulas:

Select the cells to process, go to Edit/Replace and enter into the Find box 0010 from the numpad while you are holding down the Alt key (you will see a blinking point in the Find box, nothing else), then enter semicolon in the Replace box, and Replace All. Then go to Data/Text to Columns, choose Delimited, Next, semicolon as the Delimiter, and in the next window enter the next column as destination (if the data begin in A2, enter B2 here). Now the first line and the second line are separated. Now select the cells in column C and do another Tex to Columns with space as Delimiter (now uncheck semicolon as delimiter) and do not change the Destination offered by excel. You will get this:

*	A	B	C	D	E
*
19 Stanley Street WODONGA VIC 3690	19 Stanley Street	WODONGA	VIC
1 ABC Street ALBURY NSW 2640	1 ABC Street	ALBURY	NSW

<colgroup><col style="width:30px; "><col style="width:253px;"><col style="width:143px;"><col style="width:93px;"><col style="width:84px;"><col style="width:104px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: center"]Street address 1[/TD]
[TD="align: center"]Town[/TD]
[TD="align: center"]State[/TD]
[TD="align: center"]Postcode[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3690[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2640[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JodiOc]

Thanks Michael M

All formuls work except for the one in D1- nothing happens when I enter this formula.

 

[Michael M]

Check to make sure it's not a double space after VIC
Also consider the solution provided by Istvan in post #6

 

[JodiOc]

Thanks Guys... most of what you recommend works. Unfortunately there are 3 spaces in between Wodonga VIC 3690, so still having some difficulty....

 

[Michael M]

3 spaces between Wodonga and VIC and 3690
OR
3 spaces in total isntead of 2 ??