Remove Line Breaks in Excel (3 Easy Ways)

You’ve likely dealt with line breaks if you work with data that is imported from databases or salesforce.

In Excel, a line break enables you to have many lines in a single cell.

The name and other portions of the address are separated by a line break in the name and address dataset shown below.

Even though it appears fantastic in the example above, there may be occasions when you need to delete the line breaks from the dataset because you don’t want them.

In this video, I’ll demonstrate three quick methods for removing line breaks from Excel (also called in-cell carriage return in Excel).

This instruction explains:
1. Use Find and Replace to eliminate line breaks.
2. Line Breaks Are Removed Using A Formula
3. Using VBA, remove line breaks
4. Carriage Return vs. Line Break

1. Use Find and Replace to eliminate line breaks.

Using Find and Replace is the simplest manual method for removing line breaks.

Applying this is simple, and Find and Replace will perform all the work by locating all the line breaks and eliminating them from the cells.

Let’s say you wish to eliminate the line breaks from a dataset of addresses like the one below.

The steps to replace line breaks with commas using Find and Replace are as follows:

Choose the dataset you wish to remove the line breaks from.

On the Home tab, click

Go to the Editing group and select “Find & Select.”

Select “Replace” from the menu that appears.

Use the keyboard shortcut Control + J and position the cursor in the “Find what” area (hold the Control key and press the J key). This inserts the line break character in the “Find what” box even though you might not see anything.

Enter a comma, followed by a space, in the replace area (, )

Select Replace All.

The aforementioned procedures would eliminate all line breaks and substitute commas for them. This will display the outcome in Excel as a single line in a cell.

If you wish to delete the line break and substitute something else for the comma, you must do so in Step 6 (in the “Replace with” field).

It is advised that you make a backup copy of the data-containing file to ensure that you don’t lose it in case you do.

2. Line Breaks Are Removed Using A Formula

Using a formula is another technique to eliminate line breaks in Excel. The formula will provide you with results that will immediately update if you make any changes to the original dataset, in contrast to the Find & Replace approach, which produces static results.

Let’s say you have the dataset depicted below and want to get rid of all the cell line splits.

The equation to accomplish this is given below:

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

The CHAR(10) character, which stands in for the line feed character, is located by the SUBSTITUTE function and replaced. Therefore, the algorithm above locates all line breaks and replaces them with blank characters, thereby eliminating them.

Use the following formula if you want the result to be separated by a comma (or any other character):

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

If you have Wrap-text enabled in the cell where the formulas result is shown, you can still see some text shifting to the next line. Line breaks are not to blame for this. If the column is modified

3. Using VBA, remove line breaks

This might be the quickest method to remove the line breaks from Excel if you are experienced in using VBA.

But before you can make it a one-click procedure, you have to put in some effort.

The VBA code that will traverse each cell in the table is provided below.

Sub RemoveLineBreaks()
For Each Cell In the Selection
Cell.Value = Replace(Cell.Value, Chr(10), ", ")
Next
End Sub

The code above utilizes VBA’s REPLACE function to replace each cell’s line break characters with a comma, followed by a space.

This VBA code produces a static outcome that cannot be changed. So, if you intend to utilize it, I strongly advise that you first duplicate the original dataset.

This code may be added as a conventional Excel module, or if you need to use it frequently and require it to be accessible in all of your Excel workbooks, you can save it in a Personal Macro Workbook or as an Excel Add-in.

You may add this macro code to the Quick Access Toolbar to utilize it with only one click. In this manner, running the code just requires that you pick it and click the macro button in the QAT.

4. Carriage Return vs. Line Break

There is little distinction between an Excel line break and a carriage return.

The purpose of a carriage return is to place the cursor at the start of the sentence. It was used in typewriters’ early iterations and later in computers. In Excel, a carriage return by itself wouldn’t result in a line break. It causes a line break when paired with Line Feed.

In Excel, line breaks are frequently accompanied by carriage returns.

Additionally, Excel only adds a line feed when you use ALT Enter to insert a line break, not a carriage return.

So why do we need to be concerned about carriage return? Considering the possibility that it was downloaded from a database that employs carriage return, you could find it in your data.

You may find and replace carriage returns using formulae. The ASCII code for a carriage return is 13 (CR, ASCII code 13).

More information on the distinction between a line feed and a carriage return may be found here.

I sincerely hope this instruction was helpful.