Comments on: 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 2013, 2010 – 2003 Continue reading →

Table of contents

Comments page 2. Total comments: 158

  1. CTRL J and Trim Function. Great.

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

  3. Thank you so much.

  4. Thank you! CTRL-J is amazing.

  5. 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.

    1. Hello!
      If I understand your question correctly, take a look at the Delete Blanks tool. It can remove the blank rows.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

      If this is not what you wanted, please describe the problem in more detail.

  6. Thank you very much!

  7. Great, many thanks

  8. Great tip! Thank you!

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

    1. restart Excel and do it again bro

      1. This helped resolve the issue, many thanks!

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

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

    Would you know where this is coming from ?
    Thanks

  11. Thanks Bro

  12. 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

  13. thank you
    شكرا

  14. It works

  15. Thank you very much, it helped :)

  16. Thanks. The formulas worked.

  17. Hi,

    You have no idea in how much you helped me with this information.

    Thank you very much man.

    God bless you !

  18. I'm trying to use the manual replacement with Ctrl+J, the dot shows up, but it says it can't find anything when clearly I see the cells have carriage returns (created by doing alt+enter) within the cell.

  19. If you want to do this with a Mac, you have to use Google sheets as an intermediary platform.

    Copy the word document table as is and paste in Google sheets. It will paste it as it is meant to be, then download the file in XLSX format.

    This should solve the problem.

  20. I just wanted this and got it. So simple and easy. Thanks.

  21. Thanks. Great tip. Very easy with the Ctrl+H Ctrl+J

  22. Great ! the trick for manual reaplacement of CR+LF,
    tnks

  23. On Excell office 2013 (my current version) if the above formula not working, you only want to change to : =SUBSTITUTE(SUBSTITUTE(B2;CHAR(13);"");CHAR(10);"")

    (my formula is only changing the comas TS formula)

  24. thank U

  25. Is there a way to do this from unix command line?

  26. The manual method is just what I needed, when I needed it. The line returns were making pasting content into a SharePoint table quite a problem, and manually removing them was not practical. CTRL+J = line return? Who knew? (well, yeah, you did) Thanks!

  27. Thanks!

  28. Excellent! thanks a lot. It saved me a ton of work for my next spreadsheet

  29. Thanks .. for great help..

  30. 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

  31. 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!

  32. 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)

  33. Thanks

  34. 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!

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

    1. Try Command instead of CTRL

  36. 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

  37. Thank You!!

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

  39. 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

  40. 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.

  41. 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),", "))

  42. Ditto. Extremely helpful.

  43. Thank you so much!! really healpfull

  44. Everthing so well explanied !
    Thanks !

  45. Brilliant

  46. 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.

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

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

  49. Thanks for the Ctrl+J tip. Works fine.

  50. 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

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