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:
=SUBSTITUTE(B5,CHAR(10),", ")
![Use Excel Functions to Remove Carriage Returns](https://www.exceldemy.com/wp-content/uploads/2021/12/How-to-Remove-Carriage-Returns-in-Excel.png)
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
<< Go Back to Carriage Return | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!