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 →

Comments page 3. Total comments: 158

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

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

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

  4. thank U

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

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

  7. Thanks!

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

  9. Thanks .. for great help..

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

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

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

  13. Thanks

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

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

    1. Try Command instead of CTRL

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

  17. Thank You!!

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

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

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

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

  22. Ditto. Extremely helpful.

  23. Thank you so much!! really healpfull

  24. Everthing so well explanied !
    Thanks !

  25. Brilliant

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

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

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

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

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

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

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

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

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

  34. DID HELP THANKS!

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

    Cheers

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

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

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

  39. very helpful! thank you so much

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

  41. very useful, thanks !

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

  43. Much gratitude!!!

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

  45. Thanks for Help.It's great.

  46. Thanks so much. This is a great tool !

  47. Any reason why 'If 0 0 Then'?

  48. 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?

    1. Solved my own problem by copying, pasting Values into a new cell, and then deleting the old cells

  49. 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?

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

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