Separating data within a cell

[thewiseguy]

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

 

[AlphaFrog]

Try something like this...

Book1
	A	B	C	D
1	Full Address	Street	City	State Zip
2	247 W Passaic St, Maywood, NJ 07607-1257	247 W Passaic St	Maywood	NJ 07607-1257
Sheet1

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

Try

Excel 2010
	D	E	F
1	address	City	zip
2	247 W Passaic St	Maywood	NJ 07607-1257.
Sheet2

Cell Formulas
Range	Formula
D2	=LEFT(A1,FIND(",",A1,1)-1)
E2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",100)),200),100))
F2	=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)))

 

[bschwartz]

Beat me to it.

 

[bschwartz]

And another one...

 

[thewiseguy]

awesome. you guys rock. thanks.