Splitting addresses into four columns (in bulk)

JodiOc

New Member
Joined
Sep 16, 2014
Messages
7
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Is
19 Stanley Street
WODONGA VIC 3690
in one cell sperated by ALT + Enter ??
 

JodiOc

New Member
Joined
Sep 16, 2014
Messages
7
It is exported from another database program. I am unable to change this format in the original program.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

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

Well-known Member
Joined
May 16, 2013
Messages
1,634
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:

*ABCDE
1*Street address 1TownStatePostcode
219 Stanley Street
WODONGA VIC 3690
19 Stanley StreetWODONGAVIC3690
31 ABC Street
ALBURY NSW 2640
1 ABC StreetALBURYNSW2640

<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

New Member
Joined
Sep 16, 2014
Messages
7

ADVERTISEMENT

Thanks Michael M

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Check to make sure it's not a double space after VIC
Also consider the solution provided by Istvan in post #6
 

JodiOc

New Member
Joined
Sep 16, 2014
Messages
7
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
3 spaces between Wodonga and VIC and 3690
OR
3 spaces in total isntead of 2 ??
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top