3 ways to remove Line Breaks in Excel

In this Excel tutorial, you will learn different ways to remove line breaks from a string.

Line break is a special character used to change lines, it is represented by Char(10) in Excel.

To manually insert a line break after a cursor press ALT+ENTER shortcut key.

3 ways are

Using Substitute function
Using Find and Replace
Using VBA

Remove line breaks using Substitute function

In the below image you can see cell A1 and cell A2 contains a string with line breaks.

In cell B1 Substitute function is used to replace line breaks with a single space.

Cell B1 formula

=SUBSTITUTE(A1,CHAR(10),” “)

In the above formula, A1 is a cell having string.

Char(10) outputs line break character (represents line break) which needs to be replaced.

” ” (single space) represents replace by character. All the line breaks in a string will get replaced by a space.

At the place of a single space, you can have your replaced by a character.

Remove line breaks using Substitute function in Excel

Remove line breaks using Find and Replace

Follow the below steps to quickly remove line breaks from a string without using a formula.

Select the cell or range
Press CTRL+H shortcut key to bring Find and Replace dialog box.

Alternatively, you can go to the Home tab and select Replace option

Find and Replace option in Excel ribbon

In Find What field place cursor then press CTRL+J to bring line break character.
In Replace with field place cursor then press spacebar one time to bring single space character.
Click on Replace All button.

Note:- After pressing CTRL+J you will notice or may not notice a small dot.

Make sure “Match entire cell contents” is not ticked in Options.

If the line breaks not getting replaced then close the workbook and try again.

Find and replace line breaks in Excel

Remove line breaks using VBA

You can also use VBA to remove line breaks from a string in Excel.

Just select range then run the below VBA code.

To use the VBA code just paste the below code section in the standard module.

If unsure about how to insert VBA code in Excel read the below post

How to insert and run VBA code in Excel?

Sub removeLineBreaks()
''will remove line breaks in selected cells
  Dim cel As Range
  For Each cel In Selection
    cel.Value = Replace(cel.Value, Chr(10), " ")
  Next cel
End Sub

VBA to remove line breaks in Excel