Only recently I made a basic template for projectplanning with an emphasis on resource profiles. I had to unpivot the profiles and subsequently had to generate the date range (grain = day) and unpivot that as well - only considering working days, of course. There were quite a bit of category columns and data types to be applied. Of course I solved this in PQ-M and none of the column headers were hard coded for robustness. Not sure that was the hardest ‘normalisation’/split I’ve ever done, but it was fun! :-)
This is really a brilliant and valuable video 😊… there are many ways to Rome (aka text splitting), but neither are so easily accessible and perfectly demonstrated as Mynda‘s ways 😊 … Loving it! ❤😊❤
Often we are instinctively use options that we are used to, without giving it a second thought. Thanks for alternative ideas on how to perform split texts.
Great synopsis, Mynda. OTOH, I would still have completely “vectorized” the TEXTSPLIT solution, because I like single cell solutions and just because we can. :-)
I recently came across unbreakable spaces while using the options listed here except for power query which I have not used yet. How can I split cells that have unbreakable spaces?
Try =textsplit(a1, char(160)). Char(160) being the non-breaking space character. Alternately you can use the substitute function to replace Char(160) with a regular space.
Hi Mynda "This isn't just a comment on this video, which was great by the way, but a big thank you for all the fantastic tips you've shared throughout the year. 🎉 Wishing you a Merry Christmas and a Happy New Year! 🎄✨"
Dear Mynda, Your split using the TEXTSPLIT function looks better if spilled: =VSTACK({"FirstName","LastName","YearBirth","MonthBirth","DayBirth"}, DROP(REDUCE("",A2:A32,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,{", ","-"})))),2)) 🤗
❓What’s the trickiest data-splitting problem you’ve faced?
Learn more about the Advanced Excel Formulas course: bit.ly/splitcells24course
Only recently I made a basic template for projectplanning with an emphasis on resource profiles.
I had to unpivot the profiles and subsequently had to generate the date range (grain = day) and unpivot that as well - only considering working days, of course. There were quite a bit of category columns and data types to be applied. Of course I solved this in PQ-M and none of the column headers were hard coded for robustness.
Not sure that was the hardest ‘normalisation’/split I’ve ever done, but it was fun! :-)
10:12 You can also use Power Query to split vertically into rows, as well!!!
Sure can! 😅
The last trick was awesome....
😁 so pleased you liked it!
Super job Mynda!
Thanks so much, Chris!
One colum vertical splitting into rows in very brilliant idea ! I liked this tip very well, Many thanks !!
Glad you found it helpful!
Brilliant thank you
Thanks for watching 🙏
Nice job. Thanks, Mynda!
Thanks so much!
Excelent! I really appreciated for your help. Basic but important to remember. Thanks a lot.
Glad it was helpful!
Thank you Myndi, What a good tips to convert from text to tables and rows. I love it.
Glad you liked it! 🙏
❤❤❤❤❤❤❤🎉 spreadsheet Excel
🙏😁
This is really a brilliant and valuable video 😊… there are many ways to Rome (aka text splitting), but neither are so easily accessible and perfectly demonstrated as Mynda‘s ways 😊 … Loving it! ❤😊❤
Thanks so much for your kind words and support!
Often we are instinctively use options that we are used to, without giving it a second thought. Thanks for alternative ideas on how to perform split texts.
It's amazing how often we get stuck in our usual ways! Glad I could offer some alternative ideas.
Great synopsis, Mynda.
OTOH, I would still have completely “vectorized” the TEXTSPLIT solution, because I like single cell solutions and just because we can. :-)
😁 thanks for watching, Geert!
I recently came across unbreakable spaces while using the options listed here except for power query which I have not used yet. How can I split cells that have unbreakable spaces?
Try =textsplit(a1, char(160)). Char(160) being the non-breaking space character. Alternately you can use the substitute function to replace Char(160) with a regular space.
Hi Mynda
"This isn't just a comment on this video, which was great by the way, but a big thank you for all the fantastic tips you've shared throughout the year. 🎉
Wishing you a Merry Christmas and a Happy New Year! 🎄✨"
Thanks so much, Ivan! Your continued support is appreciated. 🥰
Dear Mynda,
Your split using the TEXTSPLIT function looks better if spilled:
=VSTACK({"FirstName","LastName","YearBirth","MonthBirth","DayBirth"},
DROP(REDUCE("",A2:A32,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,{", ","-"})))),2)) 🤗
Nice formula! Thanks for sharing, Jose.