Go Back

Remove Line Breaks in Excel

Figure 1. of Remove Line Breaks in Excel.

In order to remove any/all Line Breaks from text located within a cell of our worksheet, we are going to utilize a formula syntax based on the CHAR and SUBSTITUTE Functions in Excel.

Generic Formula

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

How to Remove Line Breaks in Excel

There are 3 different and simple ways to remove Line from text in Excel.

      1. Removing Line Breaks by using the SUBSTITUTE Function.

We are going to make use of the SUBSTITUTE Function which is able to locate matching text inside any cell and then replace such with any specific text of your choosing.

In the illustration below, we are going to remove any Line Breaks from our text and replace them all with commas.

Figure 2. of Substitute Function in Excel.

The formula syntax which we have assigned to cell C2, in the example above, is as follows;

=TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),","))

This will automatically remove any unwanted line Breaks, replace them with commas and give us the results displayed in the 1 LINE OUTPUT column of our worksheet illustration.

      2. Removing Line Breaks by using the CLEAN Function.

The Excel CLEAN Function operates by taking a text string and returning it free from any line breaks and other unwanted characters.

Generic Formula

=CLEAN(text)

  • We enter the following formula in cell C3 =CLEAN(B3)

Figure 3. of Clean Function in Excel.

This will automatically remove any unwanted characters from the text in column B of our worksheet.

      3. Removing Line Breaks by using the TRIM Function.

The Excel TRIM Function operates in the same manner as the CLEAN Function.

Generic Formula

=TRIM(text)

  • We enter the following formula in cell C4 =TRIM(B4)

Figure 4. of Trim Function in Excel.

This will automatically remove any unwanted spaces or line breaks from the text in column B.

In conclusion, Excel has three built-in Functions – SUBSTITUTE, TRIM and CLEAN – that are designed to automatically remove any extra space and line breaks from text.

Figure 5. of Final Result.

Instant Connection to an Expert through our Excelchat Service

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have some cells that have unwanted line breaks. I concatenated data together into one cell but there were some rows that were blank that caused the line breaks. I either need to fix the formula to skip blank cells on concatenation or I need to remove the line breaks in the combined cell. Help! On tight deadline and my data files are failing on upload due to this.
Solved by O. A. in 21 mins
I was just on with someone and my session ended. didn't have the option to add more credits. Can I get back on with the consultant? This was my question... I have some cells that have unwanted line breaks. I concatenated data together into one cell but there were some rows that were blank that caused the line breaks. I either need to fix the formula to skip blank cells on concatenation or I need to remove the line breaks in the combined cell. Help! On tight deadline and my data files are failing on upload due to this.
Solved by F. C. in 16 mins
My page breaks are frozen up and I can't move them or remove them (using Excel 2016).
Solved by Z. E. in 27 mins

Leave a Comment

avatar