Find and Replace Line Breaks in Excel (6 Examples)

Random anonymous addresses are one example of having line breaks. We will remove these line breaks and replace them with different characters.

Method 1 – Using the Find and Replace Command to Replace Line Breaks in Excel

Steps

Copy the cell content from the range of cells B5:B8 to the range of cells C5:C8.

Select the cells in column C and press Ctrl + H.

A new window will open named Find and Replace. Go to the Replace tab, and in the Find what field, press Ctrl + Shift + J.
A line break will be inserted, but it shows only as a tiny dot in the field.
In Replace with, type the expression with which you want to replace the line break. We will enter “–“. If you want to replace the line break with nothing, keep this field empty.
Press Replace all.

All the line breaks in the range of cells C5:C8 are removed.

Note:

In some cases, pressing Ctrl + Shift + J may not work. In that case, try Ctrl + J or insert the line-break character manually.

Method 2 – Utilizing the SUBSTITUTE Function to Find and Replace Line Breaks

Steps

Select cell C5 and enter the following formula:

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

The line breaks from B5 were replaced with “;” in cell C5.

Drag the Fill Handle icon at the corner of cell C5 to cell C7.
This fills all the cells with the appropriate formula and replaces the line breaks.

How Does the Formula Work?

1. SUBSTITUTE(B5, CHAR(13),””): This function will replace line break with nothing. This part is necessary for both UNIX and WINDOW operations.

2. SUBSTITUTE(SUBSTITUTE(B5, CHAR(13),””), CHAR(10),”, “): This formula will remove line break and replace line break with “,”.

Method 3 – Inserting the TRIM Function to Find and Replace Line Breaks

Steps

Select cell C5 and enter the following formula:

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

The content from cell B5 has been copied to C5 while replacing every line break with ”,”.

Drag the Fill Handle down.

Find and Replace Line Breaks in Excel using Trim function

How Does the Formula Work?

1. SUBSTITUTE(SUBSTITUTE(B5, CHAR(13),””), CHAR(10),”, “): This formula will replace line break with “,” in the case of both Windows and UNIX carriage return/ line feeds combinations.

2. TRIM(SUBSTITUTE(SUBSTITUTE(B5, CHAR(13),””), CHAR(10),”, “)): TRIM function will make sure that there will be no extra space and lines won’t join.

Method 4 – Inserting the CLEAN Function to Remove Line Breaks in Excel

Steps

Select cell C5 and enter the following formula:

=CLEAN(B5)

This copies the content from B5 to C5 and removes non-printable characters such as line breaks.

Drag the Fill Handle icon down.

Read More: How to Do a Line Break in Excel

Method 5 – Applying the Wrap Text Command to Delete Line Breaks

Steps

Copy cells B5:B7 to the range of cells C5:C7.

Select the range of cells C5:C7.
Click on the Wrap Text icon from the Alignment group in the Home tab.

This will convert all the line breaks to spaces.

Read More: How to Space Down in Excel

Method 6 – Applying VBA Macro to Find and Replace Line Breaks

Steps

Go to the Developer tab, then click Visual Basic.

Click Insert and Module.

In the Module window, enter the following code:

Sub linebreak_replace()

    Dim Mr As Range

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

End Sub

Note:

This VBA code will apply to the whole worksheet.
Here, “_” removes and replaces line breaks. To replace the line break with some other character, edit the part inside the Replace function.