Download the example workbook
This tutorial will demonstrate how to remove line breaks from the text in Excel & Google Sheets.
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.
Also, you can replace the line breaks with any other character/s, such as with comma and space:
Using TRIM Function
The TRIM function can also be used to remove the unwanted spaced or line breaks from the text.
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:
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: