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.

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.

DataText to ColumnsDelimitedOther

CTRL + j as a delimiter.


Gord Dibben MS Excel MVP

On Wed, 24 Jun 2009 21:07:01 -0700, 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.

I would copy the data into word and then do Find and Replace
If you click the More button on the Replace window you will see options to
allow you to find things like returns.
Ethan


"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.