This tutorial will demonstrate how to remove line breaks from the text in Excel & Google Sheets.

Remove Line Breaks in Excel

A line break is something by which you can have multiple lines in the same cell. Sometimes it can become unnecessary. There are several functions that can be used to remove line breaks from the text: SUBSTITUTE, CLEAN, and TRIM.

Using SUBSTITUTE Function

The SUBSTITUTE function finds and replaces text in a cell. By combining it with the CHAR Function, we can find and replace line breaks with a single space.

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

Remove Line Breaks with SUBSTITUTE

Also, you can replace the line breaks with any other character/s, such as with comma and space:

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

Remove Line Breaks with Comma

Using TRIM Function

The TRIM function can also be used to remove the unwanted spaced or line breaks from the text.

=TRIM(B3)

Remove Line Breaks using TRIM Function

Using CLEAN Function

The CLEAN function will remove any undesirable characters from the text.

To remove any undesirable characters (line breaks) from the text using the CLEAN function, we’ll use the following formula:

=CLEAN(B3)

Remove Line Breaks using Clean Function

The drawback of using the TRIM or CLEAN function to remove line breaks from the text is that it doesn’t replace the line breaks with commas or any other character.

Remove Line Breaks In Google Sheets

The formula to remove the line breaks from the text works exactly the same in Google Sheets as in Excel:

Remove Line Breaks in Google Sheets