Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.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!
You just saved me hours of frustration.
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
Hallelujah! Blessings to you and your household! 🙂
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
Hi
Did u fins the solution for your comment.
if yes please suggest me the same thing.
Nice - who would have guessed Ctrl-J? Thank you.
This is a fantastic solution. I am so glad I found your website. Thank you.
You need a cape.
Hi, I tried Ctrl J and it doesn't work on imac excel 2008. Any other?
You are the greatest!! This trick saved me from tons of misery. 🙂
Thank you so much!
Thanks a Ton. Great Help
Thank you 🙂
Thank you, thank you!!!!!
Wow, you have contributed to society more than you realize. Thank you sir!!
Genius
hello, i tried this but i cannot type more than one character in the "Other" field, Any suggestions?
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.
Why control J? Who cares I guess, you just saved me a lot of time. Thanks!
Thank you Sir!
Wow - TY
What should be used to achieve this in TEXTJOIN function?
You can use "CHAR(10)"
I couldn't get CHAR(10) to work as a TEXTJOIN delimiter, either with or without quotation marks. Has anyone tested this?
hero !
Wow, just amazing, I was about to create a perl script, this saved a lot of time.
anyway to get this to work on Mac versions?
I'm trying to find the equivalent for the mac version as well. any luck?
YOU DA BOMB!
U R a GOAT for sharing this trick!! Awesome 🙂
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!
saved me from bangin my head
Thaaaaaanks.
Great!! Thank you!!
For Mac check out this post:
https://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/mac-excel-2011-text-to-column-carriage-return-as/6cbde588-05b9-4c8d-95ea-1a83476e3a09
Days of work in Minutes...Thank you so much GOAT for sure
Great solution...thx for sharing the geniosity!
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.
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.
Will it be work vertically? I tried but not succeed.
Let me know if any solution to do.
Omaga, THANK YOU.
Absolutely AWESOME
Thank you so much!
Brilliant!!!
doesn't work
Worked Great- Thank you!
Thank you so much. Thank goodness so many people are smarter than me. Appreciate you.
Excellent, saved me hours of work