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

G

Guest

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

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)
 

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

Similar Threads

Replacing carriage returns with a comma 4
How do i hide carriage returns? 1
Delete Carriage Returns 4
Getting rid of carriage return in cell 4
Remove carriage returns in an Excel document? 3
copying/pasting accurately from a Word table (carriage returns) 1
how do i format carriage returns for each cell in excel? 1
Carriage returns - how to get rid of them (again) 8

Top