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

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

  2. very useful, thanks !

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

  4. Much gratitude!!!

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

  6. Thanks for Help.It's great.

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

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

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

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

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

  12. thanks, it works

  13. Yeah so the first method doesnt work anymore.

  14. Thanks!
    You saved my day!

    Anna

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

    Thanks.

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

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

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

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

  20. Thanks Alexander. Solution 1 worked for me.

  21. Thanks a lot !!!

  22. pedro louro method worked great! Thanks!

  23. Excellent!

  24. Your macro was excellent thanks!

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

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

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

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

  29. Very helpful! Thank you so much!

  30. Incredibly helpful - thanks much

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

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

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

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

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

  35. hi how to add new line character?

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

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

  37. Neat! thanks.

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

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

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

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

  40. Thank you for the macro! It really helped.

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

  42. Thanks A lot

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

  44. Worked perfect, thanks.

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

  46. Thanks. It helps.

  47. Thanks, the clean() information was helpful.

  48. Thanks for the great VBA macro it worked perfectly!

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

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

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

    1. Hello Howard,
      Please send a sample workbook with your data to support@ablebits.com and we'll try to help you.

      1. Thanks, Ill send you a sample across, Howard

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