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]

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]

ADVERTISEMENT

Try this

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;;">19 Stanley Street
WODONGA VIC 3690</td><td style=";">19 Stanley Street</td><td style="color: #333333;;">WODONGA  </td><td style=";">VIC</td><td style="text-align: right;;">3690</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,FIND(<font color="Red">CHAR(<font color="Green">10</font>),A1,1</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">A1,B1,""</font>),D1,""</font>),E1,""</font>),CHAR(<font color="Red">10</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D1</th><td style="text-align:left">=TRIM(<font color="Blue">LEFT(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">A1," ",REPT(<font color="Teal">" ",100</font>)</font>),200</font>),100</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=RIGHT(<font color="Blue">A1,4</font>)</td></tr></tbody></table></td></tr></table><br />

 

[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
1	*	Street address 1	Town	State	Postcode
2	19 Stanley Street WODONGA VIC 3690	19 Stanley Street	WODONGA	VIC	3690
3	1 ABC Street ALBURY NSW 2640	1 ABC Street	ALBURY	NSW	2640

<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>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JodiOc]

ADVERTISEMENT

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 ??