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.

15 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!!

Leave a Reply

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