Thank you Mynda for this introduction to the new super functions! Can't wait for these to be available generally, and know that we are already prepared for them through your video! Keep up the great work you are doing! 😊👍
Excellent summary, Mynda. Awesome new functions. 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 change the setting in File | Options | Advanced | Editing options and remove the checkmark for 'After pressing Enter'.
@@MyOnlineTrainingHub I found out that the automatic movement downwards was originally for data entry. But a long while ago it changed so that the arrow keys can do the data entry too. So if you type a number and then down-arrow (or any arrow!) it will invoke the number and move the cursor to the next cell. Thus the auto-movement behaviour is rendered redundant. So I turned it off.
I use the enter key on the number keypad when entering data so it suits me that enter moves down to the next cell, but I can see it being redundant if you don't use that often.
Hi Mynda. Thanks for the great array constant {} tricks! Been waiting a long time for better text functions. Finally, can retire FILTERXML/SUBSTITUTE combination to split text. And with the optional arguments, the new functions are so much more flexible and useful! Thanks for demonstrating. Thumbs up!!
Hi Mynda!Great Tutorial, Really Exciting Times With All The New Functions Microsoft Have Introduced To Excel.Looking Forward To Trying Them Out When The Become Available...Thank You :)
I'd be interested to understand what you're struggling with and try to help you solve it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you Mynda foe this amazing trick. I'm not able use those r the formulas in my excel and currently using MS excel version is 2019 in my system. Kindly suggest...
Hi again, Mynda. To rephrase my question of yesterday: with the Data Text To Columns feature one can specify the data type of the extracted chunks so I wondered if there was an undocumented aspect of TEXTSPLIT that offered the same functionality? All the chunks from TEXTSPLIT are naturally TEXT but it would be cool to have the numeric and date parts to be re-cast all within a single formula. My VALUE addition to your example does work but .....
Great question, Nigel. There currently isn't a data type parameter, as you know. I'll ask the Excel team if there are any plans for Excel to correctly detect data types on splitting the text. In the meantime, you can replace VALUE with the double unary - - : =IFERROR(--TEXTSPLIT(C61,{",","/","("},")",TRUE,""),TEXTSPLIT(C61,{",","/","("},")",TRUE,""))
Hi Mynda, I added an Example 6 to your TEXTSPLIT: =LET(MyFormula,TEXTSPLIT(C61,{"/","(",","},")",TRUE,""), IFERROR(VALUE(MyFormula),MyFormula)) Is there a more efficient way of getting those numbers rather than text? Great vid as usual!
First of all, congrats on such a great job. I have a question concerning your textsplit example 2.1 (nested textsplits for obtaining only the city names). I believed that Textbefore(Textsplit(C14, ","),"/") would do the trick and that Textsplit(Textsplit(C14, ","),"/") would return an error. Probably, this was due to a lack of comprehension, on my side, on how array formulas, spills e nested array formulas work. Could you please be so kind as to explain why the nested textsplits work? Many thanks
It shows my alleged comprehension when nesting textsplit in textbefore (but not when nesting textsplit in textsplit; to me, it should return an error 😥😥😥😥)
Very nice, but I just don't understand why in example 2.1, with the nested TEXTSPLIT, everything after the forward slash disappears, where I would expect it to split where the slash is. Can you explain?
@@MyOnlineTrainingHub Aha. The frustrating thing is that I just can't get a grasp on how and why this works. I found that TEXTBEFORE(TEXTSPLIT(C15,","),"/") gives the same result and this is more "graspable" to me. Interesting thing here is that this formula gives the right array when selecting it in the formula bar, where the double TEXTSPLIT does not. Anyway, many thanks for your quick answer!
Is there a formula to split date/time? I have been using right or left, but with the difference between 3/3/22 2:23 and 3/13/22 12:23 month over month. Will text split work if it looks for a space?
If your date/time values are proper date serial numbers then you can use the following: Date: =INT(A1) Time: =A1-INT(A1) Format cells as date or time. If your date/time values are text, then yes, you can use the space character as the delimiter with TEXTBEFORE/TEXTAFTER/TEXTSPLIT Mynda
@@MyOnlineTrainingHub excellent! Thank you so much. I've recently gotten into inventory and we don't have anything like this, so I'm making it my project for the time being :)
Hi Marcus, it's available in 365. I'm not sure if it's still only available in the beta version. Microsoft never give a date for general availability as this could change while a function is in beta testing.
@@MyOnlineTrainingHub Ah thank you , as there where a few updates to 365 this week , but i havent seen any changes yet. Nothing to do than wait for a while longer .
Thank you for showing these off! So much easier than LEFT, RIGHT, SEARCH, and LEN combinations to extract what I need.
My pleasure, Cody! Glad you'll be making us of them when they come out.
TEXTSPLIT is a fantastic new formula that will save so much time and effort for Excel users. Thanks for the in depth demonstrations Mynda 👍
Glad you like it!
Thank you Mynda for the demo. The TEXTSPLIT function is just awsome !
My pleasure, Alex 😊
I can't wait until these become generally available. Thanks Mynda!
Glad you like them!
Thank you Mynda for this great introduction to the new text functions!
My pleasure, Denton!
These new functions are amazing. Very good Mynda. Thank you!
You're so welcome, Luciano!
Really good! These are way better than the original text functions. Thank you!
I agree, Neeti!
Thank you Mynda for this introduction to the new super functions! Can't wait for these to be available generally, and know that we are already prepared for them through your video! Keep up the great work you are doing! 😊👍
Thanks so much, Vijay! Glad you're looking forward to the new functions 😊
My favorite excel lady.
😁 glad you liked it, Steve!
WOW! These functions are incredible.
thanks for this video. 🙏🏼
Aren't they just! 🎉
Thank You for demonstrating, as always clear and instructive
Looking forward to releasing
Thanks, Ivan 😊
Excellent summary, Mynda. Awesome new functions. 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 change the setting in File | Options | Advanced | Editing options and remove the checkmark for 'After pressing Enter'.
Thank you! I can also press CTL+ENTER, but most of the time I forget to do that 😂
@@MyOnlineTrainingHub I found out that the automatic movement downwards was originally for data entry. But a long while ago it changed so that the arrow keys can do the data entry too. So if you type a number and then down-arrow (or any arrow!) it will invoke the number and move the cursor to the next cell. Thus the auto-movement behaviour is rendered redundant. So I turned it off.
I use the enter key on the number keypad when entering data so it suits me that enter moves down to the next cell, but I can see it being redundant if you don't use that often.
Hi Mynda. Thanks for the great array constant {} tricks! Been waiting a long time for better text functions. Finally, can retire FILTERXML/SUBSTITUTE combination to split text. And with the optional arguments, the new functions are so much more flexible and useful! Thanks for demonstrating. Thumbs up!!
Cheers, Wayne! Glad you're looking forward to using them 😊
Hy Mynda, good lessons 👍👍
Glad you enjoyed it 😊
Excellent & much appreciated
So nice of you, Drew!
Thank you Mynda for this video
You are so welcome!
Thank You Very Much.
Pleasure 😊
These are so cool. I can't wait to get these soon, thanks Mynda!
Glad you like them, Chris!
Hi Mynda!Great Tutorial, Really Exciting Times With All The New Functions Microsoft Have Introduced To Excel.Looking Forward To Trying Them Out When The Become Available...Thank You :)
Great to hear, Darryl!
Thank you.
You're welcome!
U are the best👍
Thanks for your kind words, Ergun!
it's very useful but not so easy to use!! Thank you anyway!
I'd be interested to understand what you're struggling with and try to help you solve it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Next up...."TextBetween". Thanks Mynda
Good idea, Matt! For now you can use TEXTSPLIT(...(TEXTSPLIT...))
Thank you
You're welcome 😊
Amazing.....thanks
Glad you liked them!
Thank you. Thiis was awesome. Only wish I had access to functions :(
They're coming, Marty!
Brilliant lesson, just wish I had studied harder at school.
Glad you liked it, Joseph!
Awesome. Hope they make upgrades to piviot tables, power pivot and dax using excel
Glad you liked it!
waiting for these to be available to my 365 version
Hopefully not much longer!
Thank you Mynda. It's just a pity that using the empty string as a separator returns an error instead of an array of all individual characters!
For that you can use this formula and copy across: =MID($A1, COLUMNS($A$1:A$1), 1)
@@MyOnlineTrainingHub Thank you and I knew it, but I would call that a work-around.
Looks like the life of a couple
Thank you Mynda foe this amazing trick.
I'm not able use those r the formulas in my excel and currently using MS excel version is 2019 in my system.
Kindly suggest...
These functions are only available to Microsoft 365 users. You'd need to upgrade your version of Excel.
These functions are now available to normal users
Yes, as of just last week! Have fun with them 😊
Hi again, Mynda. To rephrase my question of yesterday: with the Data Text To Columns feature one can specify the data type of the extracted chunks so I wondered if there was an undocumented aspect of TEXTSPLIT that offered the same functionality? All the chunks from TEXTSPLIT are naturally TEXT but it would be cool to have the numeric and date parts to be re-cast all within a single formula. My VALUE addition to your example does work but .....
Great question, Nigel. There currently isn't a data type parameter, as you know. I'll ask the Excel team if there are any plans for Excel to correctly detect data types on splitting the text. In the meantime, you can replace VALUE with the double unary - - :
=IFERROR(--TEXTSPLIT(C61,{",","/","("},")",TRUE,""),TEXTSPLIT(C61,{",","/","("},")",TRUE,""))
Hi Mynda,
I added an Example 6 to your TEXTSPLIT: =LET(MyFormula,TEXTSPLIT(C61,{"/","(",","},")",TRUE,""),
IFERROR(VALUE(MyFormula),MyFormula))
Is there a more efficient way of getting those numbers rather than text? Great vid as usual!
Great idea, Nigel!
Hello, blogger. I watched your video. Very good. Do you need sponsorship?
As usual great demonstration. On the side, what are the "+" and "-" signs and the "1" and "2" on the left side of the spreadsheet?
Thank you! The +/- etc. are group buttons: www.myonlinetraininghub.com/excel-group-and-outline-data
First of all, congrats on such a great job.
I have a question concerning your textsplit example 2.1 (nested textsplits for obtaining only the city names).
I believed that Textbefore(Textsplit(C14, ","),"/") would do the trick and that Textsplit(Textsplit(C14, ","),"/") would return an error.
Probably, this was due to a lack of comprehension, on my side, on how array formulas, spills e nested array formulas work.
Could you please be so kind as to explain why the nested textsplits work?
Many thanks
Both formulas work, which shows your comprehension 😊
It shows my alleged comprehension when nesting textsplit in textbefore (but not when nesting textsplit in textsplit; to me, it should return an error 😥😥😥😥)
First 😍
Very nice, but I just don't understand why in example 2.1, with the nested TEXTSPLIT, everything after the forward slash disappears, where I would expect it to split where the slash is. Can you explain?
I think it's a spill restriction. i.e. there's nowhere for the remaining data to do, so it's discarded.
@@MyOnlineTrainingHub Aha. The frustrating thing is that I just can't get a grasp on how and why this works. I found that TEXTBEFORE(TEXTSPLIT(C15,","),"/") gives the same result and this is more "graspable" to me. Interesting thing here is that this formula gives the right array when selecting it in the formula bar, where the double TEXTSPLIT does not. Anyway, many thanks for your quick answer!
Thank you for the great videos...could I ask how you insert filters in your videos that are only visible when you click on the cell?
If you're referring to the +/- buttons, those are group buttons: www.myonlinetraininghub.com/excel-group-and-outline-data
@@MyOnlineTrainingHub thank you very much!
Is there a formula to split date/time?
I have been using right or left, but with the difference between 3/3/22 2:23 and 3/13/22 12:23 month over month.
Will text split work if it looks for a space?
If your date/time values are proper date serial numbers then you can use the following:
Date: =INT(A1)
Time: =A1-INT(A1)
Format cells as date or time.
If your date/time values are text, then yes, you can use the space character as the delimiter with TEXTBEFORE/TEXTAFTER/TEXTSPLIT
Mynda
@@MyOnlineTrainingHub excellent!
Thank you so much. I've recently gotten into inventory and we don't have anything like this, so I'm making it my project for the time being :)
how to add these new functions in our excel program which actually doesn't have these functions?
The only way to get these functions is to get the latest version of Excel with a Microsoft 365 license.
Just a question when is this textsplit option available ?
Hi Marcus, it's available in 365. I'm not sure if it's still only available in the beta version. Microsoft never give a date for general availability as this could change while a function is in beta testing.
@@MyOnlineTrainingHub Ah thank you , as there where a few updates to 365 this week , but i havent seen any changes yet.
Nothing to do than wait for a while longer .
As of July 2024, these are not available at all on Google Sheets.
I guess Excel was first with these functions. I expect Sheets will catch up eventually.
answer please
I am in a different time zone to you, so patience with my replies would be appreciated. Thanks for understanding.