How to Delete Returns in Excel
The pesky carraige return and new line characters, while providing a way to present text for human viewing, are not always desirable when working in a spreadsheet program like Microsoft Excel or OpenOffice.org.
In Excel, a bit of code provided by Ivan Moala on mrexcel.com, works wonders. Here is the code:
'Created by Chip Pearson
'Cleans up data by removing tabs and carriage returns in worksheet cells.
Sub CleanUp()
Dim TheCell As Range
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub
Here’s how to use it:
- In Excel click Tools > Macro > Visual Basic Editor (or just hit Alt-F11)
- In the top-left pane under the Project heading, double-click the name of the sheet you want to strip all the newlines and carriage returns from.
- In the window that pops up, paste the code above into the empty box under where you see General and Declarations.
- Run the macro by clicking the “play” button above, or hit F5
Warning: make a backup copy of your Excel workbook before performing the above procedure.
Posted under Excel, Microsoft, Uncategorized
This post was written by Content Curator on November 16, 2009
