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

[sra]

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




--

[Dave Peterson]

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

sra wrote:
>
> 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
>
> --
>

--

Dave Peterson