How to Remove Line Breaks in Excel (4 Simple Ways)

Line breaks inside cells can cause formatting problems, make it difficult to sort or filter data and cause problems when exporting or analyzing data in Excel. These hidden breaks are frequently caused by copying and pasting from other sources or by pressing Alt + Enter. Thankfully, Excel offers a number of simple methods for cleaning up your data and eliminating line breaks easily.

Excel’s built-in Find and Replace tool is the most effective option for eliminating line breaks in Excel. It takes the shortest amount of time to give you your desired output and the process is also very simple.

Below is our sample dataset where we can clearly see multiple line breaks in the column for address. It is taking up more space and looks messy. So, we will now remove these line breaks using Excel’s built-in Find and Replace function.

➤ Select the column in which you want to eliminate the line breaks. In this case, select the Address column in column B.

➤ Press  CTRL  +  H  to open the Find and Replace dialogue box.

➤ Put the cursor beside the Find What field and press  CTRL  +  J  . You will see a blinking dot which means the Find What function has identified the line break characters in the column.

➤ Now, in the Replace With field, we will put a comma (,) character and a space so that the words are separated with a comma and space character when the line break is removed. Then click on Replace All.

➤ We can see the entire address is now in one single line, only separated by a comma and space. The line breaks are completely removed from the cells.

Another easy and quick method for this is using the SUBSTITUTE function in excel. In the arguments of the SUBSTITUTE function, input the cell number, CHAR(10) function which represents the line break and then the characters you want to replace the line break with (comma or space).

Here is how you can use the formula with the SUBSTITUTE function to eliminate line breaks:

➤ First, you need to create a new column to input the formula in. Here, we are naming column C as Address without line breaks where we will apply the formula.

➤ In cell C2, input the formula:

➤ Click and drag the fill handle until cell C7 to apply the formula in all the cells of column C.

➤ We can see the entire address is now in one single line, only separated by a comma and space. The line breaks are completely removed from the cells in column C.

The CLEAN function removes all non-printable characters from text. Since line break is such a non-printable character, this function removes it too. On the other hand, the TRIM function removes all the extra spaces that might have been there due to the line break. It ensures single spaces between words.

We will wrap the CLEAN function with the TRIM function by following the steps below:

➤ First, you need to create a new column to input the formula in. Here, we are naming column C as Address without line breaks where we will apply the formula.

➤ Click on cell C2 and type the formula:

Here, the CLEAN function is for removing line breaks and the TRIM function is for fixing any extra space issues between each word.

➤ Click and drag the fill handle until cell C7 to apply the formula in all the cells of column C.

The entire address is now in one single line. The line breaks are completely removed from the cells in column C. But this method does not incorporate any additional comma or space between the words like the previous methods.

This method is ideal if you need to clean multiple cells regularly or want a reusable solution.

Here is a detailed guide on how you can use the VBA Macro to remove line breaks:

➤ Open your Excel Workbook and press  ALT  +  F11  to open the Visual Basic Editor window.

➤ Right click on the worksheet containing your data then go to Insert > Module.

➤ In the new module, copy and paste the following code.

Sub RemoveLineBreaks()
    Dim cell As Range
    For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = Replace(cell.Value, Chr(10), " ")
        End If
    Next cell
End Sub

➤ The code will look like this in the module:

➤ From the File, select Close and Return to Microsoft Excel option.

➤ Select the column you want to apply the macro to remove the lines. Go to the Developer tab.

➤ From the Developer tab, select Macros at the top left side.

➤ Select the Macro name RemoveLineBreaks then click on Run.

➤ The VBA macro will now remove all the line breaks from the cells in column B.

Frequently Asked Questions

What Causes Line Breaks to Appear in Excel Cells?

If you have copy pasted any data into an Excel cell from an external source containing carriage returns or hidden characters, line breaks can appear. It also appears when you press  ALT  +  Enter  while typing in a cell.

Can I Replace Line Breaks With Something Other Than a Space?

Yes, you can replace line breaks with any character or any text of your choice. Use this formula =SUBSTITUTE(A1, CHAR(10), ” “) and input any character or text you want inside the ” ” part. The character will then replace the line break.

Is It Possible to Remove Line Breaks in Multiple Cells at Once?

Yes, you can remove line breaks in multiple cell at once by-

• Using Find and Replace across a cell range
• Dragging a formula down a column via fill handle
• Applying VBA macro to a selection of cells.

Wrapping Up

Managing line breaks in Excel may appear to be a minor issue, but it can rapidly become a nuisance, particularly when working with multi-line addresses, imported files, or pasted data. The good news is that there is a technique to fit every user level and task complexity to deal with this.

Whether you choose a formula-driven approach like SUBSTITUTE or TRIM-CLEAN, a simple manual fix using Find and Replace, or a more sophisticated tool like VBA, you can definitely get the job done.