You’ll NEVER Struggle with These Text Functions in Excel Again

[@circadiandream]

Thank you for showing these off! So much easier than LEFT, RIGHT, SEARCH, and LEN combinations to extract what I need.

[@MyOnlineTrainingHub]

My pleasure, Cody! Glad you'll be making us of them when they come out.

[@dentonhooper1447]

Thank you Mynda for this great introduction to the new text functions!

[@MyOnlineTrainingHub]

My pleasure, Denton!

[@Up4Excel]

TEXTSPLIT is a fantastic new formula that will save so much time and effort for Excel users. Thanks for the in depth demonstrations Mynda 👍

[@MyOnlineTrainingHub]

Glad you like it!

[@alexbigonnet5708]

Thank you Mynda for the demo. The TEXTSPLIT function is just awsome !

[@MyOnlineTrainingHub]

My pleasure, Alex 😊

[@OzduSoleilDATA]

WOW! These functions are incredible. thanks for this video. 🙏🏼

[@MyOnlineTrainingHub]

Aren't they just! 🎉

[@Luciano_mp]

These new functions are amazing. Very good Mynda. Thank you!

[@MyOnlineTrainingHub]

You're so welcome, Luciano!

[@vitruvian_man]

I can't wait until these become generally available. Thanks Mynda!

[@MyOnlineTrainingHub]

Glad you like them!

[@neetichana6668]

Really good! These are way better than the original text functions. Thank you!

[@MyOnlineTrainingHub]

I agree, Neeti!

[@vijayarjunwadkar]

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! 😊👍

[@MyOnlineTrainingHub]

Thanks so much, Vijay! Glad you're looking forward to the new functions 😊

[@ivanbork4175]

Thank You for demonstrating, as always clear and instructive Looking forward to releasing

[@MyOnlineTrainingHub]

Thanks, Ivan 😊

[@drsteele4749]

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]

Thank you! I can also press CTL+ENTER, but most of the time I forget to do that 😂

[@drsteele4749]

@@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.

[@MyOnlineTrainingHub]

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.

[@AYMAN-zg2bu]

Thank you Mynda for this video

[@MyOnlineTrainingHub]

You are so welcome!

[@chrism9037]

These are so cool. I can't wait to get these soon, thanks Mynda!

[@MyOnlineTrainingHub]

Glad you like them, Chris!

[@wayneedmondson1065]

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!!

[@MyOnlineTrainingHub]

Cheers, Wayne! Glad you're looking forward to using them 😊

[@darrylmorgan]

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 :)

[@MyOnlineTrainingHub]

Great to hear, Darryl!

[@drewbbc7074]

Excellent & much appreciated

[@MyOnlineTrainingHub]

So nice of you, Drew!

[@steventate9276]

My favorite excel lady.

[@MyOnlineTrainingHub]

😁 glad you liked it, Steve!

[@imannurul2816]

Hy Mynda, good lessons 👍👍

[@MyOnlineTrainingHub]

Glad you enjoyed it 😊

[@karunakargm4139]

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...

[@MyOnlineTrainingHub]

These functions are only available to Microsoft 365 users. You'd need to upgrade your version of Excel.

[@cedjulemckeever]

As usual great demonstration. On the side, what are the "+" and "-" signs and the "1" and "2" on the left side of the spreadsheet?

[@MyOnlineTrainingHub]

Thank you! The +/- etc. are group buttons: www.myonlinetraininghub.com/excel-group-and-outline-data

[@vramarathnam]

Thank You Very Much.

[@MyOnlineTrainingHub]

Pleasure 😊

[@acinik]

U are the best👍

[@MyOnlineTrainingHub]

Thanks for your kind words, Ergun!

[@prof.dr.alvarohernandez9467]

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

[@MyOnlineTrainingHub]

Both formulas work, which shows your comprehension 😊

[@prof.dr.alvarohernandez9467]

It shows my alleged comprehension when nesting textsplit in textbefore (but not when nesting textsplit in textsplit; to me, it should return an error 😥😥😥😥)

[@massiveattackdrop]

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?

[@MyOnlineTrainingHub]

If you're referring to the +/- buttons, those are group buttons: www.myonlinetraininghub.com/excel-group-and-outline-data

[@massiveattackdrop]

@@MyOnlineTrainingHub thank you very much!

[@averagejon9677]

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?

[@MyOnlineTrainingHub]

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

[@averagejon9677]

@@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 :)

[@nigelfisher1556]

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 .....

[@MyOnlineTrainingHub]

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,""))

[@lorenakademar5267]

it's very useful but not so easy to use!! Thank you anyway!

[@MyOnlineTrainingHub]

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

[@unitedcell4198]

Amazing.....thanks

[@MyOnlineTrainingHub]

Glad you liked them!

[@gerbherb8215]

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]

I think it's a spill restriction. i.e. there's nowhere for the remaining data to do, so it's discarded.

[@gerbherb8215]

@@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!

[@Make_it_happen_in_your_life]

Thank you.

[@MyOnlineTrainingHub]

You're welcome!

[@zzzzzzzzzzzzzzzz9]

Awesome. Hope they make upgrades to piviot tables, power pivot and dax using excel

[@MyOnlineTrainingHub]

Glad you liked it!

[@manis1438]

Thank you

[@MyOnlineTrainingHub]

You're welcome 😊

[@martyf.8088]

Thank you. Thiis was awesome. Only wish I had access to functions :(

[@MyOnlineTrainingHub]

They're coming, Marty!

[@nigelfisher1556]

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!

[@MyOnlineTrainingHub]

Great idea, Nigel!

[@KrishnaKumar-zn9kg]

These functions are now available to normal users

[@MyOnlineTrainingHub]

Yes, as of just last week! Have fun with them 😊

[@Joseph-jy5lz]

Brilliant lesson, just wish I had studied harder at school.

[@MyOnlineTrainingHub]

Glad you liked it, Joseph!

[@marcusfranconium3392]

Just a question when is this textsplit option available ?

[@MyOnlineTrainingHub]

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.

[@marcusfranconium3392]

@@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 .

[@janelie2006]

Hello, blogger. I watched your video. Very good. Do you need sponsorship?

[@sakuranooka]

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!

[@MyOnlineTrainingHub]

For that you can use this formula and copy across: =MID($A1, COLUMNS($A$1:A$1), 1)

[@sakuranooka]

@@MyOnlineTrainingHub Thank you and I knew it, but I would call that a work-around.

[@samimsedeqey8231]

how to add these new functions in our excel program which actually doesn't have these functions?

[@MyOnlineTrainingHub]

The only way to get these functions is to get the latest version of Excel with a Microsoft 365 license.

[@avantikanatasha]

First 😍

[@mattschoular8844]

Next up...."TextBetween". Thanks Mynda

[@MyOnlineTrainingHub]

Good idea, Matt! For now you can use TEXTSPLIT(...(TEXTSPLIT...))

[@KV21A]

waiting for these to be available to my 365 version

[@MyOnlineTrainingHub]

Hopefully not much longer!

[@dalemcdowell5953]

As of July 2024, these are not available at all on Google Sheets.

[@MyOnlineTrainingHub]

I guess Excel was first with these functions. I expect Sheets will catch up eventually.

[@regismaltais8269]

Looks like the life of a couple

[@samimsedeqey8231]

answer please

[@MyOnlineTrainingHub]

I am in a different time zone to you, so patience with my replies would be appreciated. Thanks for understanding.