Replace hard return with a delimiter?



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

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