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.
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! 😊👍
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 :)
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 :)
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...
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!
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 😥😥😥😥)
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,""))
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
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 .
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!
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.
Thank you Mynda for the demo. The TEXTSPLIT function is just awsome !
My pleasure, Alex 😊
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 this great introduction to the new text functions!
My pleasure, Denton!
I can't wait until these become generally available. Thanks Mynda!
Glad you like them!
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!
WOW! These functions are incredible.
thanks for this video. 🙏🏼
Aren't they just! 🎉
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.
Thank You for demonstrating, as always clear and instructive
Looking forward to releasing
Thanks, Ivan 😊
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 😊
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 😊
Thank you Mynda for this video
You are so welcome!
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!
Hy Mynda, good lessons 👍👍
Glad you enjoyed it 😊
My favorite excel lady.
😁 glad you liked it, Steve!
Excellent & much appreciated
So nice of you, Drew!
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 :)
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.
Awesome. Hope they make upgrades to piviot tables, power pivot and dax using excel
Glad you liked it!
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
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!
U are the best👍
Thanks for your kind words, Ergun!
Thank You Very Much.
Pleasure 😊
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 😥😥😥😥)
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,""))
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!
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
Brilliant lesson, just wish I had studied harder at school.
Glad you liked it, Joseph!
Thank you. Thiis was awesome. Only wish I had access to functions :(
They're coming, Marty!
Next up...."TextBetween". Thanks Mynda
Good idea, Matt! For now you can use TEXTSPLIT(...(TEXTSPLIT...))
Thank you.
You're welcome!
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.
Thank you
You're welcome 😊
These functions are now available to normal users
Yes, as of just last week! Have fun with them 😊
Hello, blogger. I watched your video. Very good. Do you need sponsorship?
Amazing.....thanks
Glad you liked them!
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 .
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.
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!
waiting for these to be available to my 365 version
Hopefully not much 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.
First 😍
Looks like the life of a couple
answer please
I am in a different time zone to you, so patience with my replies would be appreciated. Thanks for understanding.