how do I remove Carriage Returns from a column in a spreadsheet?

[Doug R]

I have some data that I am manipulating in Excel to do an export to another
application. I need to be able to do a universal find and replace to get rid
of instances of Carriage Returns and Line Feeds within the data. I have
tried the basic find and replace functions but I don't know how to make them
find a carriage return character.

Any help would be greatly apreciated.

 

[Nick Hodge]

Doug

You could use a 'helper' column and the SUBSTITUTE function

This one replaces line feeds with nothing

=SUBSTITUTE(C2,CHAR(10),"")

Use CHAR(13) for carriage returns

You can then Edit>Paste special...>Values once you have achieved both
operations

Put a space between the two double-quotes to enter a space instead of
nothing
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)