How to Remove Carriage Returns in Excel: 3 Easy Ways

Method 1 – Delete Carriage Returns Using Find and Replace

Steps:

Select the cells containing line breaks.

Press Ctrl+H from the keyboard. The Find and Replace window will pop up. Go to Find what field and press Ctrl+J. A dot (.) will show up in the box. Keep Replace with field blank. Click the Replace All

All the line breaks will be removed.

Method 2 – Use Excel Functions to Remove Carriage Returns

Steps:

Enter the following formula in a blank cell:

=SUBSTITUTE(B5,CHAR(10),", ")

Use Excel Functions to Remove Carriage Returns

The SUBSTITUTE function replaces existing text with new text in a text string. However, the CHAR function returns the character specified by the code number from the character set for your computer.

Here, the formula removes line breaks of Cell B5 and replaces those breaks with commas. On the other hand, Char(10) function returns a line break character.

You will get the following result. Use Autofill (+) to copy the formula to other cells.

Method 3 – Erase Carriage Returns Using VBA in Excel

If you do not want to use manual ways or formulas to remove line breaks, using VBA can be an excellent option. The following sample dataset will be used for illustration.

Steps:

Go to the sheet containing the dataset and open the code window by clicking the View Code.

Enter the following code to the code window.

Option Explicit
Sub RemoveCarriageReturns()

Dim MyRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each MyRange In ActiveSheet.UsedRange
If 0 < InStr(MyRange, Chr(10)) Then
MyRange = Replace(MyRange, Chr(10), "")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub