Try Char(10) or Char(13)

Withyou data in cell A1 somthing like
B1
=LEFT(A1,FIND(CHAR(10),A1)-1)
C1
=MID(SUBSTITUTE($A1,B1,),2,FIND(CHAR(10),SUBSTITUT E($A1,B1,),2)-2)
and so on

If this post helps click Yes
---------------
Jacob Skaria


"Erin." wrote:

I have an Excel 2003 file with the full US address in one column. Street and
city are seperated by a hard return as are state and zip. Ultimately I need
to have four columns of data: street, city, state, zip. I can not figure out
how to accomplish that. The hard returns between street and city and then
state and zip act as the delimiter but I can't seem to get Excel 2003 to
acknowledge the hard return as a delimiter to parse out the data. I can use
the clean function to remove the hard returns but then I lose the only
delimiter I have. Essentially I am looking for a way to replace the hard
returns with another delimiter. Any suggestions would be greatly appreciated
for this first time poster. Thank you.