Convert to columns: Carriage-return delimiter

I found out the other day that the carriage return can be used as a delimiter when converting text to columns. This is useful if you have in-cell carriage returns that you want to get rid of. (To do an in-cell carriage return in the first place, you just hit Alt+Enter.)

Highlight your column of data, making sure the columns to its right are empty. On the Data ribbon, hit Text to Columns. Hit the Delimited option, and then check the Other option. Click in the box where you are to type the delimiter and press CTRL+J.

Hey presto, text to columns!

This entry was posted in How to. Bookmark the permalink.

40 Responses to Convert to columns: Carriage-return delimiter

  1. Spread Thin says:

    You just saved me hours of frustration.

  2. clev says:

    That was f'in awesome thank you very much that was extremely helpful hopefully you will see this comment.

    I will probably never visit this site again but it was great explaination

  3. babymama says:

    Hallelujah! Blessings to you and your household! 🙂

  4. Hannah says:

    Hi this didnt work for me, it just kept the top line and deleted the other lines.
    Any help would be greatly appreciated. I'm using Office Home and Business 2013

  5. Steve Kiessling says:

    Nice - who would have guessed Ctrl-J? Thank you.

  6. Theresa Schultheis says:

    This is a fantastic solution. I am so glad I found your website. Thank you.

  7. Ellen Horn says:

    You need a cape.

  8. E Crespo says:

    Hi, I tried Ctrl J and it doesn't work on imac excel 2008. Any other?

  9. FinanceSRG says:

    You are the greatest!! This trick saved me from tons of misery. 🙂
    Thank you so much!

  10. Kapil says:

    Thanks a Ton. Great Help

  11. SoHappy says:

    Thank you 🙂

  12. Angie W says:

    Thank you, thank you!!!!!

  13. googlesearchmaven says:

    Wow, you have contributed to society more than you realize. Thank you sir!!

  14. mork says:

    hello, i tried this but i cannot type more than one character in the "Other" field, Any suggestions?

  15. Tim says:

    This is a great tip.

    For those like me who are still struggling, please note that this works perfectly for carriage returns entered in Excel using Alt+Return or Alt+Enter. It didn't work on the csv file that I was working on. I got the problem mentioned above where only the first line of each cell is preserved.

    If I find a solution for files that do not originate in Excel, I will report back.

  16. Collette Tucker says:

    Why control J? Who cares I guess, you just saved me a lot of time. Thanks!

  17. Luis says:

    Thank you Sir!

  18. Cheski Baum says:

    Wow - TY

  19. RK says:

    What should be used to achieve this in TEXTJOIN function?

  20. ab says:

    Wow, just amazing, I was about to create a perl script, this saved a lot of time.

  21. Thomas Wilk says:

    anyway to get this to work on Mac versions?

  22. Andrew says:

    YOU DA BOMB!

  23. T Brown says:

    U R a GOAT for sharing this trick!! Awesome 🙂

  24. liz says:

    Thank you! I spent 20 minutes trying to figure out a solution, then hit the internet. First couple of sites were no help but your solution worked like a charm and saved me a couple of hours of work!

  25. Anshul says:

    saved me from bangin my head

  26. Raj says:

    Great!! Thank you!!

  27. Simon says:

    Days of work in Minutes...Thank you so much GOAT for sure

  28. Tina says:

    Great solution...thx for sharing the geniosity!

  29. Tom says:

    Firstly thanks for the Ctrl+J heads up

    Secondly I had the same issue with only returning the first columns as above - apparently it's unprintable characters in the string so I did a find and replace (using the Ctrl+J) to swap out the return characters for commas, then saved as a csv to get rid of any junk in the string, then did text to columns.

    Was a bit fiddly but only took a few mins and definitely worth it over manual editing.

  30. Drew says:

    For those who are having problems with it just resulting in the top line of data:

    Copy all of the data and paste it into a new sheet using
    Paste Special > Values. (Alt+E, S, V > Enter).
    It should work then.

  31. Vinu Jose says:

    Will it be work vertically? I tried but not succeed.
    Let me know if any solution to do.

  32. Yoohoo says:

    Omaga, THANK YOU.

  33. Kev Lee says:

    Absolutely AWESOME

    Thank you so much!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.