Video: How to remove line breaks in Excel

Learn about the formulas and tools you can use for this simple task in Excel: remove line breaks and get your data ready for work.

Remove line breaks in excel: video transcript

Line breaks can be handy for a better view, but not when you need to use your values. In this video we'll look at several simple ways of deleting carriage returns without resorting to VBA.

Use the Find tool in Excel: replace carriage return

Replace with Excel built-in Find tool

If you want to remove carriage returns just once, you can use the standard Find and Replace tool in Excel. You can open it from the Home tab or press Ctrl + H on your keyboard. To enter the line break character into the "Find what" field, you need to hold the Ctrl key and press J on your keyboard.

You will see the cursor change to a little dot. Make sure there is nothing in the "Replace with" field, or enter the symbol you want instead, and click Replace all.

Use formulas in Excel to remove carriage returns

It wouldn't be Excel if you couldn't use formulas for the task. You will need a new column and we'll try three different functions for slightly different purposes.

  1. Perhaps the simplest formula you can use is =CLEAN that will remove all non-printing characters, including line breaks, from the cell you enter in brackets.
  2. Another function that works great for replacing carriage returns is the Substitute function. Enter
    =SUBSTITUTE(C2,CHAR(10),", ")

    There are two character codes you can use, 10 and 13. If you work in Windows, you need character code 10. To add another one to the same formula, include another Substitute function, wrap the one you have in brackets. Enter the other character code and the replacement.
    Add another character code with a formula

  3. If you introduce other symbols, you can make sure no extra spaces appear by including the TRIM function into this formula. Here is what you'll get: TRIM and our current formula enclosed in parentheses.
    =TRIM(SUBSTITUTE(SUBSTITUTE(C2, CHAR(10),", "), CHAR(13), ""))

Now that you have your formula, you need to apply it to other cells. As you may know, the quickest way to do it is pointing to the lower-right corner of the pattern cell until you see a black plus sign, and double-clicking it. Now you can copy the results, and paste them back as values that you can use for your work.

Remove carriage returns in Excel with Cell Cleaner

Let me also show you how to remove line breaks or any other extra characters with the help of Cell Cleaner, our special add-in for Excel. It comes with 4 tools that solve all basic issues with data formatting.

  • This time we'll go to Ablebits Utilities tab and look for the Convert text tool.
  • Select the column with your records and pick the third option: "Convert line break to:". Now it's your choice whether you want to replace it with spaces, a combination of characters, or if you prefer to remove it by leaving this field blank.
  • Click Convert and get results.
    Convert line-breaks with the Convert Text tool

Feel free to use the coupon code PREVIEW to get Cell Cleaner or the entire Ultimate Suite with a discount.

Line breaks in Excel shouldn't be an issue for you anymore, but if you have any questions, please post them as comments. We'll do our best to help you.

You may also be interested in

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)