Replace hard return with a delimiter?

[Erin.]

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.

 

[Jacob Skaria]

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),SUBSTITUTE($A1,B1,),2)-2)
and so on

If this post helps click Yes

 

[Gord Dibben]

Data>Text to Columns>Delimited>Other

CTRL + j as a delimiter.


Gord Dibben MS Excel MVP

 

[Ethan Strauss]

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