5 Ways to Make Your Excel Data Usable (Splitting Cells)

  Рет қаралды 24,900

MyOnlineTrainingHub

MyOnlineTrainingHub

Күн бұрын

Пікірлер
@MyOnlineTrainingHub
@MyOnlineTrainingHub 7 күн бұрын
❓What’s the trickiest data-splitting problem you’ve faced? Learn more about the Advanced Excel Formulas course: bit.ly/splitcells24course
@GeertDelmulle
@GeertDelmulle 7 күн бұрын
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! :-)
@SloTownDave
@SloTownDave 2 күн бұрын
Thank you so very much for what you do. You have helped all of us immensely.
@bestamazondeals2614
@bestamazondeals2614 2 күн бұрын
A very helpful tutorial video, thank you mam....🌹👍
@yogirajoo
@yogirajoo 6 күн бұрын
One colum vertical splitting into rows in very brilliant idea ! I liked this tip very well, Many thanks !!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
Glad you found it helpful!
@therock6010
@therock6010 Күн бұрын
Thank you so much, great support
@buddhilalchaudhary4949
@buddhilalchaudhary4949 7 күн бұрын
Thank you Myndi, What a good tips to convert from text to tables and rows. I love it.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
Glad you liked it! 🙏
@karllinguistik7244
@karllinguistik7244 7 күн бұрын
Excelent! I really appreciated for your help. Basic but important to remember. Thanks a lot.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 7 күн бұрын
Glad it was helpful!
@happymystic9800
@happymystic9800 6 күн бұрын
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! ❤😊❤
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
Thanks so much for your kind words and support!
@ivanbork4175
@ivanbork4175 7 күн бұрын
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! 🎄✨"
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
Thanks so much, Ivan! Your continued support is appreciated. 🥰
@chrism9037
@chrism9037 7 күн бұрын
Super job Mynda!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
Thanks so much, Chris!
@ziggle314
@ziggle314 7 күн бұрын
Nice job. Thanks, Mynda!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
Thanks so much!
@nikhilphul
@nikhilphul 6 күн бұрын
The last trick was awesome....
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
😁 so pleased you liked it!
@JJ_TheGreat
@JJ_TheGreat 6 күн бұрын
10:12 You can also use Power Query to split vertically into rows, as well!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
Sure can! 😅
@teoxengineer
@teoxengineer 5 күн бұрын
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?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 5 күн бұрын
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.
@kkravch
@kkravch 6 күн бұрын
Thanks. More than one delimiter in PQ as with Excel?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 5 күн бұрын
Yes, you can specify multiple delimiters.
@brettburton1477
@brettburton1477 7 күн бұрын
Brilliant thank you
@MyOnlineTrainingHub
@MyOnlineTrainingHub 7 күн бұрын
Thanks for watching 🙏
@khurramyaqub1568
@khurramyaqub1568 5 күн бұрын
Superb
@MyOnlineTrainingHub
@MyOnlineTrainingHub 5 күн бұрын
Thank you! 🙏
@GeertDelmulle
@GeertDelmulle 7 күн бұрын
Great synopsis, Mynda. OTOH, I would still have completely “vectorized” the TEXTSPLIT solution, because I like single cell solutions and just because we can. :-)
@MyOnlineTrainingHub
@MyOnlineTrainingHub 7 күн бұрын
😁 thanks for watching, Geert!
@BaSYaVSkI
@BaSYaVSkI 6 күн бұрын
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
@MyOnlineTrainingHub 6 күн бұрын
It's amazing how often we get stuck in our usual ways! Glad I could offer some alternative ideas.
@tomekleddo8537
@tomekleddo8537 4 күн бұрын
Hi Mynda, what is your favourite font for Excel?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 күн бұрын
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. 🤦‍♀️
@tomekleddo8537
@tomekleddo8537 2 күн бұрын
@@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!
@100dollarpie
@100dollarpie 3 күн бұрын
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. 🎅
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 күн бұрын
Thanks for sharing. That is a faster way to do it. Happy holidays to you too!
@cruzianfibergeek
@cruzianfibergeek 6 күн бұрын
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?
@MikeStice
@MikeStice 6 күн бұрын
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.
@MahmoudAllam-vn8mh
@MahmoudAllam-vn8mh 5 күн бұрын
"Please, I want an Excel list for beginners." 🙏🙏🙏🙏🙏🙏🙏
@MyOnlineTrainingHub
@MyOnlineTrainingHub 5 күн бұрын
You can get started with this video: kzbin.info/www/bejne/nKvHn2SsmK2DqaM
@MahmoudAllam-vn8mh
@MahmoudAllam-vn8mh 5 күн бұрын
@@MyOnlineTrainingHub Think you ❤
@JoseAntonioMorato
@JoseAntonioMorato 7 күн бұрын
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
@MyOnlineTrainingHub 6 күн бұрын
Nice formula! Thanks for sharing, Jose.
@JoseAntonioMorato
@JoseAntonioMorato 4 күн бұрын
@@MyOnlineTrainingHub The formula you liked improved: =REDUCE({"FirstName","LastName","YearBirth","MonthBirth","DayBirth"},A3:A32,LAMBDA(a,v, VSTACK(a,TEXTSPLIT(v,{", ","-"})))) 🤗
@nazarkamal8831
@nazarkamal8831 6 күн бұрын
❤❤❤❤❤❤❤🎉 spreadsheet Excel
@MyOnlineTrainingHub
@MyOnlineTrainingHub 6 күн бұрын
🙏😁
7 Advanced PivotTable Techniques That Feel Like Cheating
16:07
MyOnlineTrainingHub
Рет қаралды 98 М.
Try This Function Instead of IF Statements
12:51
Kenji Explains
Рет қаралды 63 М.
The evil clown plays a prank on the angel
00:39
超人夫妇
Рет қаралды 53 МЛН
Cat mode and a glass of water #family #humor #fun
00:22
Kotiki_Z
Рет қаралды 42 МЛН
Copilot in OneDrive -  Who thought THIS was possible?!
11:34
Leila Gharani
Рет қаралды 138 М.
The Excel Tool That Does What PivotTables Can’t (File Included)
13:59
MyOnlineTrainingHub
Рет қаралды 174 М.
5 Conditional Formatting Hacks That Will Blow Your Mind
11:16
Mike’s F9 Finance
Рет қаралды 9 М.
The Excel Functions Almost Everyone Overlooks (Better Than SUMIFS)
10:29
MyOnlineTrainingHub
Рет қаралды 114 М.
DOUBLEXLOOKUP... the Excel function you've been waiting for!
12:29
Excel Off The Grid
Рет қаралды 30 М.
Simplify ANY Excel Formula With This Trick
14:17
Kenji Explains
Рет қаралды 33 М.
The AI Spreadsheet We've All Been Waiting For
10:45
Kevin Stratvert
Рет қаралды 192 М.
7 Excel Features EVERYONE Should Know
13:53
MyOnlineTrainingHub
Рет қаралды 34 М.
5 Excel Secrets You'll Be Embarrassed You Didn't Know
17:32
Excel Campus - Jon
Рет қаралды 270 М.
Change These 10 Settings Before You Use Excel Again
12:44
Kenji Explains
Рет қаралды 76 М.