How to remove carriage returns (line breaks) from cells in Excel 2016, 2013, 2010

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 2016, 2013 - 2003

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.

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

Note: 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.

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

You may also be interested in:

101 Responses to "How to remove carriage returns (line breaks) from cells in Excel 2016, 2013, 2010"

  1. Luca says:

    Just a little mistake. You wrote: (LF, ASCII code 13) instead of (LF, ASCII code 10)

    Thank you for this useful article :-)

  2. Franco says:

    I used the macro and it was really good because I don't have the question mark in a box anymore :-) but now, the macro is deleting all the formulas I had in workbook and leaving only the answers :-(.

    • Alexander says:

      Hi Franco,

      The point is that my macro processed all the formulas on the sheet and not only those that contained a line break, my apologies. I've fixed the macro, please copy it anew from the article.

      If the result of the formula outputs a carriage return, I'm afraid nothing can be done about this. A macro cannot find where exactly in the formula the carriage return occurs, that is why it handles the formula's result and not the formula per se.

  3. daniel says:

    Thanks!!!

  4. Brian says:

    I'm trying to save an excel sheet to csv and I keep getting an extra line. I tried using your VBA method and couldn't get that to work (I'm assuming because I'm saving it to csv) and when I try the find and replace method it says no matches are found. Any other thoughts on why I keep getting an extra line?

    Thanks

  5. Rob says:

    Thank you SO much! This was driving me nuts, and formula #2 worked perfectly!

  6. Palani says:

    Fantastic! I was breaking my head for a long time before I landed in your page! Keep up the good work!

  7. Pravin S says:

    Nice.I am ver Happy!!!
    I am able to solve the issue with this
    Using
    ReplaceSubstring(CStr(nabGroupDoc.InternetAddress(0)),Chr(10),"")
    ReplaceSubstring(CStr(nabGroupDoc.InternetAddress(0)),Chr(13),"")

    Thanks a lot.

  8. Howard says:

    Hi Pravin
    I followed Solution 1, replacing 'carriage returns' with a 'comma'. I did this as I wanted to split addresses, originally in contained in a single cell, across columns using 'Text to columns', and thought the comma was a useful delimiter. The replace function appeared to work, however when I went on to use 'Text to columns' only the first piece of the text (before the comma) appeared in my original cell, no text in the columns to the right as I would usually expect. Can you help please? The data was sent to me in an Excel worksheet, but was originally extracted from an Access based database. Kindest regards, H

  9. Lmarr says:

    The same thing happened to me as it did to Pravin above: "I followed Solution 1, replacing 'carriage returns' with a 'comma'. I did this as I wanted to split addresses, originally in contained in a single cell, across columns using 'Text to columns', and thought the comma was a useful delimiter. The replace function appeared to work, however when I went on to use 'Text to columns' only the first piece of the text (before the comma) appeared in my original cell, no text in the columns to the right as I would usually expect. Can you help please? The data was sent to me in an Excel worksheet, but was originally extracted from an Access based database. Kindest regards, H"
    Could you please post if there is a solution. I find the only way around this is to Ctrl-x and Ctrl-v in the fx line. Very tedious when I have thousands to do.

  10. Mike says:

    Thanks for the great VBA macro it worked perfectly!

    Any ideas on how to remove/replace strike through text?

  11. Jason Friedman says:

    Thanks, the clean() information was helpful.

  12. Grace Lim says:

    Thanks. It helps.

  13. mrox says:

    So helpful! (Used the formula)
    Big thanks!!!

  14. Zeke says:

    Worked perfect, thanks.

  15. Priyanna says:

    Thank you, it worked perfectly! Saved me a lot of trouble.

  16. Hussein says:

    Thanks A lot

  17. Pratik says:

    Thanks a ton!!!! It's just marvelous :)

  18. JC says:

    Thank you for the macro! It really helped.

  19. Sam Cranwell says:

    Unfortunately I cannot get either the formula or the macro to work correctly on Excel for Mac 2011.

    I wish to use the formula to replace the line breaks with a comma and space. The formula supplied is missing a parenthesis off the end, but even so, with that added the formula only removes the line breaks. The extra comma and space are not being inserted.

    So I tried running the VBA but this had no effect at all.

    Is there likely to be any differences in using Excel for Mac?

    • Sam+Cranwell says:

      Ok, partly answering my own question here but:

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

      has worked for me. So I'm handling only Mac line breaks. The combination version of the formula seemed to omit the comma and space

  20. Pat says:

    The VBA code seemed to work at first, though a space seemed better than an empty string, but now I get a Run-time error '13': Type mismatch on the If 0 < InStr(MyRange, Chr(10)) Then statement.

    • Mark says:

      Re:Pat and "Run-time error '13'". I had the same issue. This script worked for me several times, then stopped working with Run-time error 13. The issue was due to new data in some of the cells. In my case it was "#N/A". I deleted any cells with that data and this script worked again.

  21. John says:

    Neat! thanks.

  22. Joe Blough says:

    These methods are great but do not seem to help with the problem I am having. Excel 2010 appends a hidden linefeed 0x0d followed by a hidden carriage return 0x0a at the end of each cell. I have a worksheet that concatenates the values in other cells to form a syntactically correct linux shell script, one shell command in each cell. Then when I output these cells to a text file so I can use them with linux, each line of my linux shell scripts has the 0x0d, 0x0a ending. The linux shell chokes on the 0x0d as I believe linux expects only a 0x0a to terminate each line of input.

    The methods here seem to remove unwanted lf and cr but not the ones excel puts at the end of every cell.

    Am I using these tools incorrectly?

    Thanks

    joe

    • Base says:

      @Joe
      Microsoft products place a CRLF to perform line breaks.
      You will need to further preprocess the file, I assume a CSV on your Linux machine to remove the Carriage returns.

      I used to have to do that when transferring files
      I suggest the *nix style command line command tr

      tr -d '\r' outfile

      Make sure you name your output file a different name to protect your document in case of error.

  23. santosh says:

    hi how to add new line character?

  24. santosh says:

    i am writing from Excel to text file. how to add new line character?

    • Base says:

      @Santosh

      adding a new line character shouldn't be hard if you have a constant character to key off of. In that case you can use substitute to replace key character with the key character and char(13)

  25. Neha says:

    CLEAN does not remove leading or trailing spaces. For this use =TRIM(CLEAN(B2))

  26. pedro louro says:

    I was having the same problem of replacing lots of CRs on a Excel Sheet
    and by 'mistake' I found a trick... is very simple and it works in Excel 2010

    CRs REPLACING:

    Select the cell range you want for replacing CRs

    In the [Find and Replace] dialog go to the [Replace] Tab
    - Click in the [Find what:] box and then type CTRL+ENTER (nothing appears)
    - In the [Replace with:] box type the replacing text... or leave empty.

    For being sure just click [find all] and after click [replace all] or whatever...

    to remove the finding text just go to [find what] and press DEL and BACKSPACE

    eh voila...
    Be happy...

  27. Farag says:

    thanks a lot
    at was successful
    I tried to get the CR by pressing ALT+13, but did not work

  28. Zack says:

    Incredibly helpful - thanks much

  29. Raj says:

    Very helpful! Thank you so much!

  30. Jimm says:

    Great idea. I've been wondering how to do that for some time but never had a compelling reason (more than curiosity). I co-worker asked today if I could do it and had a huge need - the CNTL-H, CNTL-J, inserted a ^ worked great.

  31. Don says:

    This was a great help. I used the Clean function to remove unprintable characters before creating a JSON file from the Excel file. Before doing this , I was cleaning the JSON file manually.. Thank You !

  32. Mike says:

    Excellent tips with easy to follow instructions...Thank you!

  33. Ravi says:

    thank you very much, it was useful for me

  34. Max says:

    Your macro was excellent thanks!

  35. Ingrid says:

    Excellent!

  36. dal says:

    pedro louro method worked great! Thanks!

  37. Francisco says:

    Thanks a lot !!!

  38. Vijay says:

    Thanks Alexander. Solution 1 worked for me.

  39. Grape says:

    Thanks so much for this! Quick and painless solution that worked the first time, which is never the case :-)

  40. Michael says:

    Excellent!! Ran the formula - Amazed IT!:-)

  41. Tom says:

    I search on CTRL-J and it works, then doesn't work - I've replicated this over and over - seems like a ludicrous Excel bug?

  42. Kalu says:

    How to know Char in Word, plz find ,and give me

  43. José Ramírez says:

    The functions it's work!! Excellent, my friend.

    Thanks.

  44. Anna Isaksson says:

    Thanks!
    You saved my day!

    Anna

  45. phillip says:

    Yeah so the first method doesnt work anymore.

  46. A.K. Enamul Haque says:

    thanks, it works

  47. dancindave says:

    Thank you very much. I tried method 1 and it worked perfectly.

  48. Chris says:

    The find and replace method worked for once cell, but not for any of the thousands of other cells that I am trying to fix. When I try to find ctl+J or ctl+ENTER, there is nothing to find, yet the cells appear to have line breaks. All text was copied from a website. Any ideas of why the one cell worked and the others didn't?

  49. Chris says:

    So I used the =CLEAN(cell#) to get the data into a new cell without any line breaks. How do I keep the newly formatted data while deleting the reference cell so that I can save the file as a text file and not have to deal with the data containing the unwanted line breaks?

  50. John says:

    Any reason why 'If 0 0 Then'?

  51. Dan says:

    Thanks so much. This is a great tool !

  52. Jatinder says:

    Thanks for Help.It's great.

  53. Paulo Cezar da Silva says:

    Thanks man. You saved my life. I have a 28,000 lines files to import in another application and a cell with a lot of line breaks. Thanks a lot.

  54. Paul says:

    Much gratitude!!!

  55. Charlene says:

    Hello,
    Can anyone help?

    When I type the code, I get a space automatically added between 'Integer: Last' (4th row down). Each time I try to remove it, vb automatically re adds a space, making my code have an error. How do I stop the space being automatically added?

    Private Sub btnRefresh_Click()
    Dim W As Worksheet:
    Set W = ActiveSheet

    Dim Last As Integer: Last = W.Range("A1000").End(xIUp).Row

    If Last = 1 Then Exit Sub
    Dim Symbols As String
    Dim I As Integer
    For I = 2 To Last
    Symbols = Symbols & W.Range("A" & I).Value & "+"
    Next I
    Debug.Print Symbols
    End Sub

  56. olivier says:

    very useful, thanks !

  57. Carl Witthoft says:

    SUggestion: rather than issuing commands to turn automatic calculation off and then on, you should retrieve the current state, then re-set to that state when done with the macro. That's more friendly than forcing either auto or manual calculation to be set.

  58. aki says:

    very helpful! thank you so much

  59. Stacy Smith says:

    Very Helpful.

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

    should be

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

    You were missing a ) at the end.

  60. Khuram Shazad says:

    I want to replace char(10) with space in above VBA Script. please help. I don't want to use formula it will not fixable to handle long data

  61. saqib says:

    Hello i need help from somebody:

    i want the detail of these below short cut keys

    1) Ctrl + E
    2) Ctrl + J
    3) Ctrl + L

    Thanks

  62. younes amraoui says:

    There is a quicker way:
    Select the cells that you want to fix
    Ctrl + H
    Find what: Ctrl + J
    Replace by: hit space

    Cheers

  63. Luis says:

    DID HELP THANKS!

  64. Stacie says:

    Can you remove the carraige returns from only certain columns instead of the whole worksheet? Sorry - very beginner understanding here!

    • JeffeVerde says:

      Yes, you can apply a Find or Find/Replace to a specific column or columns. Select the target column(s) first, and then open Find. The Find or Find/Replace will be limited to the highlighted area.

  65. Harrison Delfino says:

    I had been using MarketXLS for this and it's great.

  66. rohan says:

    Hi, I have data as per below and i want date to be updated Horizontally. Can You please help me to update the same:

    Product 6a Product 6b Product 6c
    Product 7a Product 7b Product 7c

    Product 6a
    Product 6b
    Product 6c

  67. JeffeVerde says:

    The issue of double-spaces can be fixed with a second pass of Find/Replace --

    1) Find Ctrl+J - Replace with , as described in this article

    2) Find - Replace with

  68. Absar says:

    Thanks for the Ctrl+J tip. Works fine.

  69. Excel User says:

    Thanks for the help.. It worked like a charm!!

  70. Dustin says:

    You're awesome! Thank you for posting this :)

  71. Krishna says:

    One of the easy way is
    a) copy all of the spreadsheet contents in a column to a text editor that is capable of replacing regex. (Note that the content with cells are double-quoted)
    b) Replace "\n" with " "
    c) copy all of the contents back to the spreadsheet.

  72. Tugrul Tumay says:

    Everthing so well explanied !
    Thanks !

  73. Andrea Castillo says:

    Thank you so much!! really healpfull

  74. Dave Roberts says:

    Ditto. Extremely helpful.

  75. Brock J says:

    Great resource.
    One of your formulas is missing a bracket:
    =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")
    should be
    =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", "))

  76. Ankit says:

    Hi,

    was just curious if you can help adding an additional command where i need to just double click(open) the vba script and it directs me to which file needs to be corrected. and when i select the file -path. Then the script automatically runs the correction in the back-end and updates the file.
    Reason to this request is, i work on really huge data sets (1gb files) and i'm a beginner. i really cannot afford opening each file correcting it- saving it and closing it.

    Please help if you can.

  77. Ben says:

    Hi,

    A very simple formula is to use the LEN formula to count the characters in the cell and then minus 1. Using the LEFT formula will then give the correct result.

    e.g. for Cell A1

    =LEFT(A1,LEN(A1)-1)

    Thanks for your idea about CTRL J

  78. Riaz Amin says:

    Thank you. So glad that you posted this solution !!

  79. Me says:

    Thank You!!

  80. srinivas says:

    I have a numbers in cell like
    12332488,
    56777777,
    45654677,
    like this more then 10,000.

    i want these numbers should display in the same cell with each line 4 numbers. please let me know if there is any solution

  81. Cris says:

    I am using Mac and the CTRL + J do not function in my laptop. Can anyone help me?

  82. Suprita Kumari says:

    Hi,
    You are doing amazing work!!
    I need your help on below requirement.
    I have an excel sheet with datas:
    Col A-Box with size 1, Col A-Boxsize-1 % full, col-3 last time items stored in the box, col 4-Box size 2 which is twice in capacity as box 1.

    Using VBA macro or Excel formula I want to do below;

    1. sort % column from higher to lower and create another Box size 1.
    2. sort date column from lower to higher
    3. combine boxes with size 1 such that total % should not exceed 180% and map it to box size2(any box as box is empty)
    e.g A1+A6 has perfect 180%
    A1(92%, 2017)and A6(88%, 2013) are having perfect % sum but dates are different.
    A1(92%, 2017 and A3(82%, 2017) are having sum very close to 180 i.e. 174% and dates are also same 2017.

    It can be multiple 2 or boxes to make perfect sum

    4. We have utilize sizes at max

    5. Also, boxes of same same dates(year) should go to target box

    6. All the reamaing can be be put into one box(% should not exceed)

    Input file:
    Box size1 %Full last date item stored Box size2
    A1 92% 4/18/2017 B1
    A2 77% 4/18/2017 B2
    A3 82% 4/18/2017 B3
    A4 91% 2/11/2013 B4
    A5 91% 2/10/2013 B5
    A6 88% 2/22/2013 B6
    A7 92% 2/22/2013 B7
    A8 10% 2/22/2013 B8
    A9 92% 6/6/2013 B9
    A10 22% 7/8/2013 B10
    A11 2% 4/18/2017 B11
    A12 12% 4/19/2017 B12
    A13 96% 4/19/2017 B13
    A14 90% 2/11/2013 B14
    A15 92% 12/28/2012 B15
    A16 92% 6/6/2013 B16
    A17 99% 2/22/2013 B17
    A18 97% 2/22/2013 B18
    A19 4% 2/22/2013 B19
    A20 5% 7/8/2013 B20

    Output file
    Box size1 %Full last date item stored Box size2
    A1 92% 4/18/2017 B1
    A3 82% 4/18/2017 B1
    A6 88% 2/22/2013 B2
    A7 92% 2/22/2013 B2
    A2 77% 4/18/2017 B3
    A18 97% 2/22/2013 B3
    A20 6% 7/8/2013 B3
    .
    .
    .
    so on

    Can you please help me on this? Thanks!

  83. Marcio says:

    Thanks

  84. WhitePawn says:

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

    Your code actually removes (Chr 10) Line Feed char i.e LF not CR which is returned by Chr(13)

  85. Rhys Flook says:

    I was looking for a way to deal with line breaks after scraping some text from the web and was able to adapt the code you provided here to my own.

    Thank you very much! I was on the verge of pulling my hair out!

  86. Jack says:

    Hi, this blog is awesome.
    How can I remove lines within each cell (multiple lines in one cell with line breaks) that are not starting with a numeric character?
    Thanks and best,
    Jack

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