How to remove carriage returns (line breaks) from cells in Excel

In this tip you'll find 3 ways to remove carriage returns from Excel cells. You'll also learn how to replace line breaks with other symbols. All solutions work for Excel 365, 2021, 2019, and lower versions.

3 ways to remove carriage returns (line breaks) from your text in Excel

There can be different reasons for line breaks occurring in your text. Usually, carriage returns appear when you copy text from a webpage, get a workbook that already contains line breaks from a customer, or you add them yourself using Alt+Enter.

In any case, what you want to do now is delete carriage returns since they don't let you find a phrase and make column contents look disorganized when you turn on the wrap text option.

Please note that initially the terms "Carriage return" and "Line feed" were used in a typewriter and meant 2 different actions, you can find more at Wiki.

Computers and text processing software were created taking into consideration the typewriter specificities. That's why two different non-printable symbols are used now to indicate line break: "Carriage return" (CR, ASCII code 13) and "Line Feed" (LF, ASCII code 10). Windows uses 2 symbols one by one: CR+LF, and LF for *NIX systems. Be careful: in Excel you can find both variants. If you import data from a .txt or .csv file, you are more likely to find Carriage Return + Line Feed. When you break a line using Alt+Enter , Excel inserts Line Feed only.

In case you get .csv files from a person who uses Linux, Unix, etc., you'll find only Line Feeds again.

All these 3 ways are really quick. Feel free to pick the one that suites you best:

Tip. For your are looking for a solution to the opposite task, then read how to quickly add a line break in Excel cell.

Remove Carriage Returns manually

Pros: the fastest way.

Cons: no any additional features :(.

Please find the steps for eliminating line breaks using Find and Replace:

  1. Select all cells where you want to remove or replace carriage returns. Select all cells where you want to remove or replace carriage returns.
  2. Press Ctrl+H to open the Find & Replace dialog box.
  3. In the Find What field enter Ctrl+J. It will look empty, but you will see a tiny dot.
  4. In the Replace With field, enter any value to replace carriage returns. Usually, it is space to avoid 2 words join accidentally. If all you need is deleting the line breaks, leave the "Replace With" field empty. Eliminating line breaks using Excel Find and Replace
  5. Press the Replace All button and enjoy the result!

Delete line breaks using Excel formulas

Pros: you can use a formula chain / nested formulas for complex cell text processing. For example, it is possible to remove carriage returns and then eliminate excess leading and trailing spaces and those between words.

Or you may need to delete carriage returns to use your text as an argument of another function without changing the original cells. For example, if you want to be able to use the result as an argument of the function =lookup ().

Cons: you'll need to create a helper column and follow many extra steps.

  1. Add the helper column to the end of your data. You can name it "1 line".
  2. In the first cell of the helper column (C2), enter the formula to remove / replace line breaks. Here you can see several helpful formulas for different occasions:
    • Handle both Windows and UNIX carriage return/ line feeds combinations.
      =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")
    • The next formula will help you replace line break with any other symbol (comma+space). In this case lines will not join and extra spaces will not appear.
      =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")
    • If you want to remove all nonprintable characters from text, including line breaks:
      =CLEAN(B2)
    Excel formula to delete carriage returns from cells
  3. Copy the formula across the other cells in the column.
  4. Optionally, you can replace the original column with the one where the line breaks were removed:
    • Select all cells in column C and press Ctrl + C to copy the data to clipboard.
    • Now pick the cell B2 and press the Shift + F10 shortcut. Then just press V.
    • Remove the helper column.

VBA macro to get rid of line breaks

Pros: Being created once, can be reused in any workbook.

Cons: you need to have the basic knowledge of VBA.

The VBA macro from the example below deletes carriage returns from all cells in the currently opened worksheet (active worksheet).

Sub RemoveCarriageReturns() Dim MyRange As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each MyRange In ActiveSheet.UsedRange If 0 < InStr(MyRange, Chr(10)) Then MyRange = Replace(MyRange, Chr(10), "") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

If you don't know VBA really well, see How to insert and run VBA code in Excel

Remove carriage returns with Text Toolkit

If you are a lucky user of our Text Toolkit or Ultimate Suite for Excel, then you do not need to waste time on any of the above manipulations. All it takes is these 3 quick steps:

  1. Select one or more cells where you want to delete line breaks.
  2. On your Excel ribbon, go to the Ablebits Data tab > Text group, and click the Convert button. Ablebits Convert tool for Excel
  3. On the Convert Text pane, select the Convert line break to radio button, type the "replacement" character in the box, and click Convert.

In our example, we are replacing each line break with a space, so you put the mouse cursor in the box and press the Enter key: Replace line breaks with spaces

As the result, you will have a neatly organized table with one-line addresses: Line breaks are removed from the selected cells.

If you are curious to try this and 60 more time-saving tools for Excel, you are welcome to download a trial version of our Ultimate Suite. You will be amazed to discover a-few-clicks solutions for the most challenging and tedious tasks in Excel!

Video: How to remove line breaks in Excel



Table of contents

151 comments

  1. Thanks a lot, it is great

  2. Really helped me out, thanks much!!

  3. THANKS THIS IS IS USEFULL

  4. i recommend your methods. thanks. Please, if possible, post on youtube as more people get benefitted.

  5. thank you very much Alex. your article saved lot of pain for me. i tried with method 1 with find replace ctrl+J (which is mentioned all over internet) but did not work for me , hence i tried method 2 with CLEAN() formula and it did worked for me. thank you !

  6. Thanks a lot. Really helpfull.

  7. Thank You

  8. In the "delete line breaks using excel formulas" section, I was able to add a helper column to duplicate a column and use the "trim" function to remove line breaks for the whole column. After copying the contents of the helper column to the clipboard, I then clicked one of the cells in the original column to use the Shift + F10 combo and then press V but it didn't remove the original column. It worked yesterday but for some reason it isn't doing it today. Please help.

  9. You are my new best friend!!

  10. You are my hero! ;-)

  11. Thank you.
    Formula Works... Great...

  12. Ctrl+J Worked like a charm. Thanks!

    • Doesn't work for me at all.

    • Doesn't work for me at all - why am I getting error on this line...

      If 0 < instr( myRange,...

      Thanks

  13. Hi There,
    Why when I tried the Ctrl+J instruction, it cannot find a match while I can see the line breaks on cells. It is CSV file, I'm not sure if it matters.

  14. All these methods work for me initially, however when I re-enter a cell by either double clicking or by pressing 'F2', all the initial line breaks are re-inserted. Is there anyway to remove them from the raw string and not just visually?

  15. CTRL J and Trim Function. Great.

  16. How do you do Ctl J on a Mac? Command J doesn't work.

  17. Thank you so much.

  18. Thank you! CTRL-J is amazing.

  19. Hi, can zou pls let me know if there is a chance to remove only EMPTY Lines. I would like to keep the text fromated to the following line but I want to get rid of empty lines only .... are empty lines having another CHARACTER CHAR so that I can still use that substitute fomrula.

  20. Thank you very much!

  21. Great, many thanks

  22. Great tip! Thank you!

  23. Dear,
    Ctrl+J worked only one time to remove line break in cell.
    can you please guide?

    • yes it only worked once for me to. Then when i try again itt says "we couldnt find anything to replace" how to fix?

    • restart Excel and do it again bro

      • This helped resolve the issue, many thanks!

  24. Hi,
    I have this error when exectuting the macro:
    Type mismatch (Error 13)

    Would you know where this is coming from ?
    Thanks

  25. Thanks Bro

  26. Thank you team, This content was very helpful for me, not only this, the "ablebits" site it self is very useful for every excel users.

    Thanks a lot

  27. thank you
    شكرا

  28. It works

  29. Thank you very much, it helped :)

  30. Thanks. The formulas worked.

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 :)