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 4. Total comments: 159

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

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

  3. Ditto. Extremely helpful.

  4. Thank you so much!! really healpfull

  5. Everthing so well explanied !
    Thanks !

  6. Brilliant

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

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

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

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

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

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

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

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

  15. DID HELP THANKS!

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

    Cheers

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

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

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

  20. very helpful! thank you so much

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

  22. very useful, thanks !

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

  24. Much gratitude!!!

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

  26. Thanks for Help.It's great.

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

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

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

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

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

  32. thanks, it works

  33. Yeah so the first method doesnt work anymore.

  34. Thanks!
    You saved my day!

    Anna

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

    Thanks.

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

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

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

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

  40. Thanks Alexander. Solution 1 worked for me.

  41. Thanks a lot !!!

  42. pedro louro method worked great! Thanks!

  43. Excellent!

  44. Your macro was excellent thanks!

  45. thank you very much, it was useful for me

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

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

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

  49. Very helpful! Thank you so much!

  50. Incredibly helpful - thanks much

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