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.

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

Leave a Reply

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