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

In Excel, a bit of code provided by Ivan Moala on, 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

Comments are closed.

More Articles: