Replace hard return with a delimiter?


E

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

Advertisements

J

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
 
G

Gord Dibben

Data>Text to Columns>Delimited>Other

CTRL + j as a delimiter.


Gord Dibben MS Excel MVP
 
Ad

Advertisements

E

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top