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.
- On the Excel worksheet select all cells that may contain a line feed to remove
- Open VBE using alt+F11
- Enter Immediate mode by pressing ctrl+G
- In the Immediate box type Selection.Replace Chr(10),”” In Immediate whenever you hit enter your command is executed.
- 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).