< Go Back

Remove line breaks

Data with text in Excel often contains line breaks. Users need to remove these line breaks to extract insights from the data. You can remove line breaks from text using the CLEAN, TRIM and SUBSTITUTE function. In this tutorial, you will learn how to remove line breaks in Excel.

How to Remove Line Breaks in Excel

You can use the CLEAN, TRIM and SUBSTITUTE function to remove line breaks in Excel. The CLEAN and TRIM function removes all line breaks and unnecessary spaces. The SUBSTITUTE function can replace the line break with other characters.

Removing Line breaks with the CLEAN Function

The CLEAN function takes a string and returns it removing line breaks and other non-printable characters. The generic formula for CLEAN is,=CLEAN(text)

Here, you provide the string you want to remove the line breaks as the text and CLEAN will remove the line breaks and return the modified string.

Example

The following example contains text with line breaks in column A. To remove the line breaks and have the result in column B:

  • Go to cell B2.
  • Write the formula,=CLEAN(A2) to the formula bar in B2.
  • Press Enter.
  • Copy the formula by dragging the little square on the bottom right of the cell from B2 to B6.  

                       Figure 1. Example of Removing Line Breaks with CLEAN

 This will remove all unnecessary characters from the text in column A.

Removing Line Breaks with the TRIM Function

The TRIM function works in the same way as the CLEAN function. It follows the syntax,=TRIM(text).

Example

To perform the same operations as the previous example with the TRIM function:

  • Select cell B2 with your mouse.
  • Assign the formula,=TRIM(A2) to B2.
  • Press Enter to apply the formula to B2.
  • Drag the fill handle from cell B2 to B6 to copy the formula.

                       Figure 2. Example of Removing Line Breaks with TRIM

Column B will now have text with no line breaks or other non-printable characters.

Removing Line Breaks with the SUBSTITUTE Function

The previous functions only remove the line breaks from the text. The SUBSTITUTE function, however, can replace the line break with other characters. To replace the line break with a comma the syntax is, =SUBSTITUTE(text, CHAR(10),”, “).

Here, you use the CHAR function to find the line break by matching the ASCII code which is 10. You replace it with a comma followed by a space from the string that you specify as the first argument.

Example

To replace the line breaks from the previous example with the SUBSTITUTE function:

  • Go to cell B2.
  • Click on it with your mouse.
  • Assign the formula,=SUBSTITUTE(A2, CHAR(10),", ") in the formula bar of cell B2.
  • Click Enter.
  • Drag the fill handle to copy the formula from cell B2 to B6.

                    Figure 3. Example of Removing Line Breaks with SUBSTITUTE

This will show the text with line breaks from column A replaced by commas in column B.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar