How do I remove multiple line feed(s) or carriage return(s)?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have carriage returns in a column as follows:

address1 CR city CR state CR zip

--and--

address1 CR address2 CR city CR state CR zip

I need the city, state and zip to end up in the same column.

Can you help?

Thanks.

sra
Excel 2003




--
 
You mean you want the city in its own column, the state in its own column and
the zip in its own column?

And your data always has 3 or 4 CR in the cell?

If yes, then (assuming the data is in A1:Axxx), then put this in B1:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))=3,"dummystring"&CHAR(10),"")&A1
(and drag down)

It just looks for the number of CR's in the cell. If there's only 3, it
prefixes the string with a dummystring.

Now everything in column B has 4 CRs.

Select Column B
edit|copy
Edit|paste special|values

Now column B is values and you can use Data|Text to columns to separate the data
into each column.

Data|Text to columns
Delimited
Choose Other
type ctrl-j in that Other box

And finish up.

You could even clean up column C (edit|replace dummystring with nothing).
 

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

Back
Top