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

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.

  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.

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.

See also

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard