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.

Read More: How to Insert Carriage Return in Excel Cell

Method 2 – Use Excel Functions to Remove Carriage Returns

Steps:

• Enter the following formula in a blank cell:

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.

Read More: Remove Carriage Return in Excel with Text to Columns

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

• Run the code. You will notice that the line breaks are removed in the dataset.

Read More: How to Find Carriage Return in Excel

Download the Practice Workbook

Related Articles

• How to Replace Carriage Return with Comma in Excel 
• [Fixed!] Carriage Return Not Working in Excel

<< Go Back to Carriage Return | Text Formatting | Learn Excel