Wow! How does anyone learn this stuff on their own? Even seemingly simple functions have these hidden features. I always learn so much with your channel. Cheers!
As an Excel enthusiast, I am so happy and excited with the fact that this fantastic software is continuously evolving and paving way to unlimited possibilities when it comes to data manipulation and analysis. MVPs like you Leila, is helping us a lot to grasp the new concepts with ease. Good job and keep it up.
Wow, I’m speechless… the last example goes beyond my imagination!! Thank you so much Leila for sharing over and over your Excel knowledge through well explained videos. You are an inspiration!
Wow...the TEXTSPLIT is a great function. The amazing part for me is the that it accommodates multi-delimiters using the curly braces. Very explicit video. Thanks for sharing Leila
This video was very helpful with the splitting of a full name in one cell, then dragged the cells down to replicate the rest of the list. Saved me another good half hour of work. Thank you so much!
Slow clap Leila. I have learned things I never knew existed in Excel with your channel. Thank you. Here's another thumbs up and saved video to my Excel playlist.
It's killing me waiting for these functions!!! I don't want to watch your videos because it makes me sad when I'm working in formulas NOW using the old methods, and the whole time I'm thinking about these delicious new formulas that I don't have yet. :-) Thanks for the info!
Hi Leila. So glad to see this! Can finally retire FILTERXML/SUBSTITUTE combination to split text. And, nice to see the additional functionality built in with the optional arguments. Count me as excited about this one. Can already think of many uses. Thanks for demonstrating. Thumbs up!!
Oh, this is outstanding. I have a lot of items that need to be treated as "one word" and I've been using no-break space, but that introduces issues when people search the data (like if somebody goes by "J T" or just a state name like "New Jersey". These functions will clean all that in a hurry.
Very much excited, but need to wait for these functions to become generally available! Until then, you are going to make us expert in their usage through your videos Leila, and thanks a lot for that! You are amazing! 😊👍
My boss wants me to put a fact sheet together for some Excel tips, this channel is the first place I always go to find ideas on cool Excel tips and tricks to share with my co-workers. I hope you don't mind Leila, but I put the URL to your channel at the bottom of that fact sheet so all my co-workers can easily find your cool tips also.
Wasn't what I needed, but I saw another of your videos for TEXTBEFORE and TEXTAFTER that did the trick! Thanks! Saved me a ton of headache trying to figure out Visual Basic for my needs. I wish I had the need to use Office products enough to take your VB course, but I'd never use it enough to remember what I learned. 😕
These new function are really terrific. Good video, Leila! Hot tip: if you want to avoid having to press up-arrow or hunt with the mouse EVERY time you invoke a formula, you can make change the setting in File | Options | Advanced | Editing options and remove the checkmark for 'After pressing Enter'.
Thanks. I used to have that as my default, but I kept getting questions about why my cell doesn't go one down when I press enter, so I changed it back so it's similar to most people. I need to remember to press control enter 😁
Hi Leila, Excellent tips. One suggestion for improvement: Instead of: =TEXTSPLIT(A2,{" ","Mr. ","Ms. "},,TRUE) you can write: =TEXTSPLIT(TEXTJOIN("*",,A2:A7),{" ","Mr. ","Ms. "},"*",TRUE) Using the row delimiter ("*") with TEXTJOIN, saves you the need to copy the formula downwards 🙂
Leila - you've got the best Excel videos out there! Thank you so much.....any updates as to when Microsoft will be distributing these new functions to Office 365 users? We need these fast!!
I remember doing a lot of workaround on the raw data that comes from the database of our 3rd party auditor. Imagine the dates (MMM DD YYYY hh:mm:ss) in are in text format with extra spaces in between. 🤦🏻♂️ Boy! It took me a while to get the text formulas functioning. Could've saved a lot of time and smaller file size if this was available back then. Now, I'm hoping to find use case scenarios in my new job. Ha! 😅 Again and again. Thank you, Leila, for sharing your knowledge to us! 😉
❤I love this. Wish I know it sooner. I always have to use text to column when I have to split name to separate columns especially those with the Dr. in front of their name. Thank you Leila for showing us this.
Would love to see them rolled out already!! Many thanks Leila, will be very useful in large organisations that have not followed good product naming conventions over the years 👍👍
Hi Leila, is it possible with TEXTSPLIT to ignore the same delimiter that is repeated later in the selected string? Example I am trying to split Toronto Maple Leafs, using a space as the delimiter. However, the words Leafs ends up in it own column. That is not the desired result. I am hoping for a column with the city name (Toronto) and a column for the Team name (Maple Leafs). Any ideas?
Thanks Leila: when you you used both column and row delimiter in single function, those were different so, split in metrics was possible. If both are same, I suggest WRAPCOLS or WRAPROWS instead upon TEXTSPLIT. Else we have to transpose along with TEXTSPLIT.
Thank you so much. I only have a question, is there a chance that Microsoft could get these new functions available for the other languages because I simply work with French language.
Hi Leila. We do not see the function for TEXTSPLIT. Nor do we have the TEXTAFTER/BEFORE function that MS suggests as an alternative. Would you be able to let your audience know why MS is only allowing these functions to "select users"? (We reached out to them and they did not disclose. We have the most updated version of MS Office 365. This function would be extremely valuable to us. Thank you ahead of time.
Nevermind. I see you added this informational link in another UA-cam tutorial. Thanks :) Availability: Current partly in the BETA channel for those in the OFFICE INSIDER program. More info is here available in the Microsoft Excel announcement: techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066
Thank you for your question. It helped me to solve my problem. I now use TEXTAFTER/BEFORE instead of TEXTSPLIT to split name from surname. Surname has spaces in between e.g. "WOLFGANG VAN HALEN"
Hello Leila, 03:28 I could not understand why Excel created an empty cell. If it chose "Mr. " as the delimiter, there was nothing behind "Mr. " right..? Does it mean Excel requires text before to spilt if nothing it creates a blank.. seems buggy right ? Your thoughts
Hey guys! Hello Leila! To use the line break as a criterion you can use the function "caract(10)" in the textsplit formula. Then, the lines on the cell will split on collumns or lines. Bye!
Note that you cannot usually use the textsplit function inside a table. However, as with your example, when you join textjoin and textsplit you can put this in a table. You can also use this in even more creative ways, like this: convert B.1.3.2.a.4 into: B.001.003.002.A.004 where the data is the same but the need is to have all the numbers in three decimals to align properly.
Grab the file I used in the video from here 👉 pages.xelplus.com/textsplit-file
Yes We Liked
Very useful function, thanks!
Dear Leila mam, when you typing the formula in B2 cell, then in cell address box showing NPER. Why?
Well done. I'm thinkin of purchasing Office 365 to take a closer look of that trim formula. ↔️🔱
@@hansklein3649 The best investment I made since two years now.
Thanks for your simple, no-nonsense way of explaining. It's always a pleasure to watch your videos.
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Thanks! Whenever I'm stuck, I search your channel first for the solution...and almost always find it!
Thank you! Glad to hear that.
Wow! How does anyone learn this stuff on their own? Even seemingly simple functions have these hidden features. I always learn so much with your channel. Cheers!
I think maybe someone who goes through each and every update that Microsoft documents on their site. Someone like leila lol
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Very nice. As an Excel instructor, I'm blown away by the new formauls AND how well you present topics. Thank you
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
ever since 1992 I have been waiting for this function! Thank you so much for demonstrating this... It used to be so tedious in the past.
It really was about time.
@@LeilaGharani not wrong... love your videos and always a great place for reference.
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Amazing function. A great alternative for "text to column" functionality of Excel.
Your videos are very informative and always on point. Thank you!
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
As an Excel enthusiast, I am so happy and excited with the fact that this fantastic software is continuously evolving and paving way to unlimited possibilities when it comes to data manipulation and analysis.
MVPs like you Leila, is helping us a lot to grasp the new concepts with ease. Good job and keep it up.
Textsplit is a great function - so many possibilities when nested with other functions as you have clearly demonstrated.
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
I liked the idea of splitting and re-joining again and it's a really good feature of multiple delimiters
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Wow, I’m speechless… the last example goes beyond my imagination!! Thank you so much Leila for sharing over and over your Excel knowledge through well explained videos. You are an inspiration!
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Leila ,right now I am struggling to find words for thank you but the way you are helping people in their professional problems is hugely admirable 🤲💞
Wow...the TEXTSPLIT is a great function. The amazing part for me is the that it accommodates multi-delimiters using the curly braces. Very explicit video. Thanks for sharing Leila
Glad it was helpful!
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
My mind is blown up to pieces by this lovely function. Ms. Leila all excel users should admire you for this free informstion. 🤩🤩🤩🤩🤩🤩🤩😍😍😍😍😍😍😍😍😍
You're very welcome!
Thanks
This video was very helpful with the splitting of a full name in one cell, then dragged the cells down to replicate the rest of the list. Saved me another good half hour of work. Thank you so much!
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
I’m so pleased and very proud of you when I see 1.68M subscribers??? Did I read that right?? The world has to know! You make EVERYBODY Excel here!! 💋
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Slow clap Leila. I have learned things I never knew existed in Excel with your channel. Thank you. Here's another thumbs up and saved video to my Excel playlist.
Thanks for the thumbs up, Gabriel!
This has just single handedly changed the way I work. Amazing! So much time saved again
Great to hear! 🙌
Leila Gharani
I am very grateful to you , you helped me a lot in improving my creativity and productivity at work.
Thanks from Egypt.
Thanks!
Thank you!
You are such a genuine and smart girl ever.
Always the best instructor i ever see in UA-cam, i m giving excel training to people and your work makes me amazing for that people..you are just hero
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Esta chica es LA MERA RIATA!!!!! I mean when it comes to MS apps, look no further! Mis respetos!!!! and a big GRACIAS!
It's killing me waiting for these functions!!! I don't want to watch your videos because it makes me sad when I'm working in formulas NOW using the old methods, and the whole time I'm thinking about these delicious new formulas that I don't have yet. :-) Thanks for the info!
After this video lesson ….. I am using this function a lot to clean up my data. Much better than text to col !!!! Thanks a lot for the wonderful video
Hi Leila. So glad to see this! Can finally retire FILTERXML/SUBSTITUTE combination to split text. And, nice to see the additional functionality built in with the optional arguments. Count me as excited about this one. Can already think of many uses. Thanks for demonstrating. Thumbs up!!
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
An excellent explanation. You make the math in the equation so easy to grasp.
Oh, this is outstanding. I have a lot of items that need to be treated as "one word" and I've been using no-break space, but that introduces issues when people search the data (like if somebody goes by "J T" or just a state name like "New Jersey". These functions will clean all that in a hurry.
The new excel functions including the “textsplit” are just awesome! Thanks for sharing, Laila!👍🏻
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Leila, you're amazing! You showed me how to do what I was doing in a faster, cleaner way. Thanks!
Got here from your udemy course on Macro and VBA. Thank you for transferring your golden knowledge!!!
These videos are never boring... thank you
Díky!
Thank you Roman!
Wonderfully explained and nice to finally have these new features. We have been waiting for this for about 20 to 30 years. Thanks for the video!
Glad you liked it Andreas!
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Very good examples of how we can put TEXTSPLIT to use. Especially the SORT option. Thank you Leila!
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Very much excited, but need to wait for these functions to become generally available! Until then, you are going to make us expert in their usage through your videos Leila, and thanks a lot for that! You are amazing! 😊👍
Always good to be prepared 😁
@@LeilaGharani how can I contact you
@@paulraj9129 you just did 😂😂
My boss wants me to put a fact sheet together for some Excel tips, this channel is the first place I always go to find ideas on cool Excel tips and tricks to share with my co-workers. I hope you don't mind Leila, but I put the URL to your channel at the bottom of that fact sheet so all my co-workers can easily find your cool tips also.
Wasn't what I needed, but I saw another of your videos for TEXTBEFORE and TEXTAFTER that did the trick! Thanks! Saved me a ton of headache trying to figure out Visual Basic for my needs. I wish I had the need to use Office products enough to take your VB course, but I'd never use it enough to remember what I learned. 😕
That is a really powerful function and your example and explanation on how to use it is just brilliant. Very helpful.
These new function are really terrific. Good video, Leila! Hot tip: if you want to avoid having to press up-arrow or hunt with the mouse EVERY time you invoke a formula, you can make change the setting in File | Options | Advanced | Editing options and remove the checkmark for 'After pressing Enter'.
Thanks. I used to have that as my default, but I kept getting questions about why my cell doesn't go one down when I press enter, so I changed it back so it's similar to most people. I need to remember to press control enter 😁
I have never seen such videos, these are brief very well explained in short time and most importantly free of cost😊
Thank you for showing this. I've been waiting for this since the 90's! Seriously, it's been that long!
This makes my job much easier. Thanks for the update.
Awesome Leila. I have learned new formula
There have been many situations where this would have saved me much time. Thank you.
Same here 😁
I have amazing moments, watching your tutorials.
Great! Please continue sharing your knowledge.
Shout out to you ma'am!!!!
This is something I can use today. Thanks!!!
Hi Leila,
Excellent tips.
One suggestion for improvement:
Instead of:
=TEXTSPLIT(A2,{" ","Mr. ","Ms. "},,TRUE)
you can write:
=TEXTSPLIT(TEXTJOIN("*",,A2:A7),{" ","Mr. ","Ms. "},"*",TRUE)
Using the row delimiter ("*") with TEXTJOIN, saves you the need to copy the formula downwards 🙂
The Text Split Function is amazing in application with ease of use.
Yes I wish I had this decades ago! You are amazing.
One of the best channels to upscale your skills 😊😊😊👍
Leila - you've got the best Excel videos out there! Thank you so much.....any updates as to when Microsoft will be distributing these new functions to Office 365 users? We need these fast!!
I love all of the samples especially the last one. 🤯🤯🤯
Excel does have awesome text functions. This video just shows a few of them! There are many others!
I remember doing a lot of workaround on the raw data that comes from the database of our 3rd party auditor. Imagine the dates (MMM DD YYYY hh:mm:ss) in are in text format with extra spaces in between. 🤦🏻♂️ Boy! It took me a while to get the text formulas functioning. Could've saved a lot of time and smaller file size if this was available back then. Now, I'm hoping to find use case scenarios in my new job. Ha! 😅
Again and again. Thank you, Leila, for sharing your knowledge to us! 😉
I'm sure it will come in handy at some point 😊
❤I love this. Wish I know it sooner. I always have to use text to column when I have to split name to separate columns especially those with the Dr. in front of their name. Thank you Leila for showing us this.
It's no wonder why you have so many subscribers. Great video, thank you.
Excellent, you have cracked it already the new function, Superb, keepit up
Amazing, to the point, accurate and very easy to understand thank you
Glad you think so!
Recently learned LET, FILTER, and XLOOKUP from your videos. Very very useful at work!!! Thanks for this one too!!!
Would love to see them rolled out already!! Many thanks Leila, will be very useful in large organisations that have not followed good product naming conventions over the years 👍👍
I was wondering. Didn't see it in my version of Excel 365.
ua-cam.com/video/1tzjF7L7ajQ/v-deo.htmlsi=vt4Faz_3Ao8c5UOO
Congratulations # You earn a subscriber. Because of your to the point and simple explanation.
Thanks for the sub!
Hi Leila, is it possible with TEXTSPLIT to ignore the same delimiter that is repeated later in the selected string? Example I am trying to split Toronto Maple Leafs, using a space as the delimiter. However, the words Leafs ends up in it own column. That is not the desired result. I am hoping for a column with the city name (Toronto) and a column for the Team name (Maple Leafs). Any ideas?
Ahhhh new tool unlocked! This is what I'm missing in my master list. Thanks.
SUPERB - as EVER... THANKS, Leila - Keep up the GREAT work! 👍👍👍
This will be fantastic. I'm looking forward to TextSplit being released to all MS365 subscribers. Thanks Leila
Me too!
Leila you are amazing! I have been watching your tutorials & I just love you!
Hi, Although I have Excel 365, the TEXTSPLIT function is not available. Do you need to add the function to Excel in some way?)
It's currently still in Beta. You have to join Office Insider to get it now.
Your teaching is amazing mam.
I enjoy all the smart videos you post Leila "Guru" Gharani!
Simply amazing, superbly superb way of describing the much needed information - Loved it.
Glad you liked it!
Thanks for explaining in simple way.
Thanks Leila: when you you used both column and row delimiter in single function, those were different so, split in metrics was possible. If both are same, I suggest WRAPCOLS or WRAPROWS instead upon TEXTSPLIT. Else we have to transpose along with TEXTSPLIT.
Cool! Thanks for sharing.
Thank you for the detailed video regarding TEXTSPLIT :)
Omg that’s just mind blowing…. What a function !!!! 🤩
Thank you "Maestra Leila", you are always teaching us great Excel things. Saludos desde México 🇲🇽
De casualidad sabes cómo viene esa función en español?
Lady, thank you so much. Easy explanation.
Awesomes new excel function, even better the way you presented it. You rock!! Thanks
Leila Sensei is wonderful. Thank you so much. It was amazing 🤩. The examples are extraordinary helpful. Thank you so Leila Jane.
Glad you liked it!
Thank you so much.
I only have a question, is there a chance that Microsoft could get these new functions available for the other languages because I simply work with French language.
Yes. They have it but it seems the names in other languages aren't finalized yet (or are subject to change)...
@@LeilaGharani OK thanks
Amazing Excel evolution
Thank you for the great examples Leila!
Thanks for your effort. Your videos helps to learn things with explanation.
Tolle Tipps, klar und einfach präsentiert - vielen Dank dafür und Grüße in meine Heimat
Hi Leila. We do not see the function for TEXTSPLIT. Nor do we have the TEXTAFTER/BEFORE function that MS suggests as an alternative. Would you be able to let your audience know why MS is only allowing these functions to "select users"? (We reached out to them and they did not disclose. We have the most updated version of MS Office 365. This function would be extremely valuable to us. Thank you ahead of time.
Nevermind. I see you added this informational link in another UA-cam tutorial. Thanks :) Availability: Current partly in the BETA channel for those in the OFFICE INSIDER program. More info is here available in the Microsoft Excel announcement: techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066
Thank you for your question. It helped me to solve my problem. I now use TEXTAFTER/BEFORE instead of TEXTSPLIT to split name from surname. Surname has spaces in between e.g. "WOLFGANG VAN HALEN"
Hello Leila, 03:28 I could not understand why Excel created an empty cell. If it chose "Mr. " as the delimiter, there was nothing behind "Mr. " right..? Does it mean Excel requires text before to spilt if nothing it creates a blank.. seems buggy right ? Your thoughts
BTW I've subscribed to your channel the easy intro to TEXTSPLIT and a couple of others I watched has sure made me a FAN!!😏
Glad to have you aboard!
OMG! I swear I love you. This is awesome. Happy New Year. So helpful. Thank you. ❤️🙌🏾
Happy new year!!
Awesome. Thanks from Kalimpong, India
This will solve a lot of problems, kudos to Microsoft for this solution
Very excited, thanks a lot for wonderful functions 🌹🌹
Hey guys! Hello Leila!
To use the line break as a criterion you can use the function "caract(10)" in the textsplit formula. Then, the lines on the cell will split on collumns or lines. Bye!
Thank you, Leila! You Are The Best
Note that you cannot usually use the textsplit function inside a table. However, as with your example, when you join textjoin and textsplit you can put this in a table. You can also use this in even more creative ways, like this: convert B.1.3.2.a.4 into: B.001.003.002.A.004 where the data is the same but the need is to have all the numbers in three decimals to align properly.
Thank you Leila! I'll try it tomorrow again on my work! :)
You do a great job explaining these!
This is a great channel, thanks for all this information!!!
Holy cow this made my day and blew my mind