Separating data within a cell

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
789
Office Version
  1. 365
Platform
  1. Windows
Hello All,

How would I take a cell that contains data such as this:

247 W Passaic St, Maywood, NJ 07607-1257.

And separating it into 3 cells - Address 1, City, State and Zip


Thank you in advance

twg
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
Try something like this...

<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Full Address</td><td style="font-weight: bold;;">Street</td><td style="font-weight: bold;;">City</td><td style="font-weight: bold;;">State Zip</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">247 W Passaic St, Maywood, NJ 07607-1257</td><td style=";">247 W Passaic St</td><td style=";">Maywood</td><td style=";">NJ 07607-1257</td></tr></tbody></table><br /><br /><table 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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">",",A2</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A2,",",REPT(<font color="Purple">" ",50</font>)</font>),50,50</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A2,",",REPT(<font color="Purple">" ",50</font>)</font>),100,50</font>)</font>)</td></tr></tbody></table></td></tr></table>
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows
Try

<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 /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">address</td><td style="font-weight: bold;text-align: center;;">City</td><td style="font-weight: bold;text-align: center;;">zip</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">247 W Passaic St</td><td style="text-align: center;;">Maywood</td><td style="text-align: center;;">NJ 07607-1257.</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">D2</th><td style="text-align:left">=LEFT(<font color="Blue">A1,FIND(<font color="Red">",",A1,1</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</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">F2</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">A1,",",REPT(<font color="Purple">" ",LEN(<font color="Teal">A1</font>)</font>)</font>),LEN(<font color="Green">A1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,523
Messages
5,529,334
Members
409,863
Latest member
stacy09
Top