Remove Linefeeds from an Excel Document

I recently received an Excel document that needed to be massaged to suit the requirements of a DCS. One of the changes required was the removal of line feeds from within cells.  There were over 500 rows nearly all with multiple line feeds so I did not want to do this manually.

Some googling revealed an easy way to do this and a cool new tool to try – immediate mode in VBE.

1. On the Excel worksheet select all cells that may contain a line feed to remove
2. Open VBE using alt+F11
3. Enter Immediate mode by pressing ctrl+G
4. In the Immediate box type Selection.Replace Chr(10),”” In Immediate whenever you hit enter your command is executed.
5. Go back to the worksheet and all line feeds are removed

Note that the argument for the Chr function is a char code. Char codes are the same as ASCII codes for the first 32 control code characters so line feed is alt+0010 or Chr(10).

References:
http://www.ozgrid.com/forum/showthread.php?t=20451