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! ❤😊❤
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! 🎄✨"
Hi Mynda, Instead of using a table to send an array to the PQ editor, we can also open the relevant array in the PQ editor by defining the name method. What is the difference between both methods?
The only difference is you'd need to define a dynamic named range so that if your data expands/contracts, the range being returned by the name also adjusts.
Great synopsis, Mynda. OTOH, I would still have completely “vectorized” the TEXTSPLIT solution, because I like single cell solutions and just because we can. :-)
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.
@@MyOnlineTrainingHub Oh, I didn't even know Aptos has been 'windowsised' for lack of better word. I used to like Segoe UI Light, now I rather stick to Montserrat. Cheers!
Hello Mynda -- in using "Text to Columns” Step 4, Customize the Split. Select both comma and space as delimiters. And then select "Treat consecutive delimiters as one." Now, when you hit Next, your cleanup is done for you. Happy Holidays. 🎅
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.
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)) 🤗
@@MyOnlineTrainingHub The formula you liked improved: =REDUCE({"FirstName","LastName","YearBirth","MonthBirth","DayBirth"},A3:A32,LAMBDA(a,v, VSTACK(a,TEXTSPLIT(v,{", ","-"})))) 🤗
❓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! :-)
Thank you so very much for what you do. You have helped all of us immensely.
A very helpful tutorial video, thank you mam....🌹👍
Thank you so much, great support
One colum vertical splitting into rows in very brilliant idea ! I liked this tip very well, Many thanks !!
Glad you found it helpful!
Thank you Myndi, What a good tips to convert from text to tables and rows. I love it.
Glad you liked it! 🙏
Excelent! I really appreciated for your help. Basic but important to remember. Thanks a lot.
Glad it was helpful!
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!
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. 🥰
Super job Mynda!
Thanks so much, Chris!
Nice job. Thanks, Mynda!
Thanks so much!
The last trick was awesome....
😁 so pleased you liked it!
10:12 You can also use Power Query to split vertically into rows, as well!!!
Sure can! 😅
Hi Mynda,
Instead of using a table to send an array to the PQ editor, we can also open the relevant array in the PQ editor by defining the name method. What is the difference between both methods?
The only difference is you'd need to define a dynamic named range so that if your data expands/contracts, the range being returned by the name also adjusts.
Thanks. More than one delimiter in PQ as with Excel?
Yes, you can specify multiple delimiters.
Brilliant thank you
Thanks for watching 🙏
Superb
Thank you! 🙏
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!
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.
Hi Mynda, what is your favourite font for Excel?
Good question. I don't have a favourite though. Aptos is growing on me, but each time they change it, it takes some getting used to. 🤦♀️
@@MyOnlineTrainingHub Oh, I didn't even know Aptos has been 'windowsised' for lack of better word. I used to like Segoe UI Light, now I rather stick to Montserrat. Cheers!
Hello Mynda -- in using "Text to Columns” Step 4, Customize the Split. Select both comma and space as delimiters. And then select "Treat consecutive delimiters as one." Now, when you hit Next, your cleanup is done for you. Happy Holidays. 🎅
Thanks for sharing. That is a faster way to do it. Happy holidays to you too!
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.
"Please, I want an Excel list for beginners."
🙏🙏🙏🙏🙏🙏🙏
You can get started with this video: th-cam.com/video/fzdn1vcHPsA/w-d-xo.html
@@MyOnlineTrainingHub Think you ❤
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.
@@MyOnlineTrainingHub The formula you liked improved:
=REDUCE({"FirstName","LastName","YearBirth","MonthBirth","DayBirth"},A3:A32,LAMBDA(a,v,
VSTACK(a,TEXTSPLIT(v,{", ","-"})))) 🤗
❤❤❤❤❤❤❤🎉 spreadsheet Excel
🙏😁