Great video!!✌ Alternative formulas for "Split 3 Names Tables" using TEXTSPLIT: First: =TAKE(TEXTSPLIT([@[Full Name]]," "),,1) Middle: =SUBSTITUTE(SUBSTITUTE([@[Full Name]],[@First],""),[@Last],"") Last: =TAKE(TEXTSPLIT([@[Full Name]]," "),,-1) alternative formula for "middle" : =IFERROR(TEXTJOIN(" ",,DROP(DROP(TEXTSPLIT([@[Full Name]]," "),,1),,-1)),"") If we have an array instead of a table, single cell formula: =LET(a,A4:A13,d," ",MAKEARRAY(ROWS(a),3,LAMBDA(r,c,LET(i,INDEX(a,r),x,TEXTBEFORE(i,d,1),y,TEXTAFTER(i,d,-1),SWITCH(c,1,x,3,y,SUBSTITUTE(SUBSTITUTE(i,x,""),y,""))))))
Thank you so much for the informative video on the new excel functions. I hope Microsoft will enhance the inconsistent behavior of textsplit function (working in a table range and not in excel table).
Thanks Conrad! Yes, that is another option. There are a lot of possibilities for combining these new functions with the old text functions (Mid, Len, etc.).
This is an amazing video 📹 Jon. I really enjoyed the fact that u showed us the old methods also. Thanks again for your hard work. By the way, I am trying to take your VBA course but it is closed now. When will it be available?
Hi Nader, thank you for your interest to our VBA course. We now have the Elevate course which also includes the VBA. Here's the link for more info www.excelcampus.com/elevate-excel-invite/
I do have and use all three functions. No favorite. Tool choice depends on the problem at hand. They each have specialized applications. But maybe a fourth function TEXTBETWEEN!! It's important to recognize that all Dynamic Array functiond have the same incompatibility withe the calc engine in tables. So MS will need to provide major changes to tables or else develop an interface between Dynsmic Arrays and the table. Maybe not so easy since they need to keep the table calc engine working with older versions of Excel.
Jon. I’m sure you get requests all the time! I appreciate your videos, they have helped me quite a bit over the years. But I am really stumped here. I am trying to create a data base of parts for an item my company produces. The spares of parts a labeled Good and broken essentially. I want to have a drop down that only returns the good parts in a list. Is this possible?
Nice video. However, I think it'll be great if you don't show us already written formulas but you write the formulas as we're watching. That way, it'll be easier to understand the logic behind the formulas.
Hi John how are you , John I am unable to find the solution one of the excel problem on Date with Customer & order value...I have to multiple answers to multiple questions for eg : New customer order atleast in the month....Could you please share your ID I will share the file with you ....Please help me in finding the solution ....
You know your videos are good when a person is watching one at Sunday dinner. Thanks J
Great video!!✌
Alternative formulas for "Split 3 Names Tables" using TEXTSPLIT:
First: =TAKE(TEXTSPLIT([@[Full Name]]," "),,1)
Middle: =SUBSTITUTE(SUBSTITUTE([@[Full Name]],[@First],""),[@Last],"")
Last: =TAKE(TEXTSPLIT([@[Full Name]]," "),,-1)
alternative formula for "middle" : =IFERROR(TEXTJOIN(" ",,DROP(DROP(TEXTSPLIT([@[Full Name]]," "),,1),,-1)),"")
If we have an array instead of a table, single cell formula:
=LET(a,A4:A13,d," ",MAKEARRAY(ROWS(a),3,LAMBDA(r,c,LET(i,INDEX(a,r),x,TEXTBEFORE(i,d,1),y,TEXTAFTER(i,d,-1),SWITCH(c,1,x,3,y,SUBSTITUTE(SUBSTITUTE(i,x,""),y,""))))))
Thank you so much for the informative video on the new excel functions. I hope Microsoft will enhance the inconsistent behavior of textsplit function (working in a table range and not in excel table).
astonishing video Excel Campus - Jon. I crushed that thumbs up on your video. Always keep up the superior work.
Thanks Jon. Your explanation helps alot!!! :) :)
Many thanks, Jon. I came across the incompatibility between spill functions and tables with another function yesterday. I hope they can fix this!
I think it's a tough one to solve for but I hope so too!
@@ExcelCampus Yes, from reading Richard Hay's comment below, it will be a difficult nut to crack.
Sir, Thank you Very Much for sharing these informative videoes. May the Lord Bless you 💝
Thank you Karim! 🙏
Superb presentation. Perhaps using the Mid and Len functions for extracting the middle names using TextBefore and TextAfter could be another option.
Thanks Conrad! Yes, that is another option. There are a lot of possibilities for combining these new functions with the old text functions (Mid, Len, etc.).
Great demo Jon! Nice to be made aware of the options and possibilities of these functions. Thanks for sharing. Thumbs up!!
Really Like The TEXTSPLIT FUNCTION.Really Looking Forward To Trying All The New Functions When Microsoft Make Them Available To 365...Thank You Jon :)
Happy to hear it Darryl. I'm looking forward to everyone getting them too. They are definitely some time savers!
Didn't know about the tables and which functions are compatible. Thank you for the tips.
You're welcome, Joshua! :)
Excellent video Jon, as professional as ever. Hopefully we'll soon be able to do spilled arrays inside tables. Thank you!!!
Thanks Ivan! I appreciate your support. And I hope for spill ranges in tables too! 👍
Hey bro! Your video means something different.
Thanks You so much.
Thanks Ada! 🙂
Thanks, and I want to ask that is it similar to FIND or SEATCH function? Thumbs up up up!!!!!
Have you tried to use an array of delimiters in all three formulas and check if they work? I am curious to see…
Thanks!
This is an amazing video 📹 Jon. I really enjoyed the fact that u showed us the old methods also. Thanks again for your hard work.
By the way, I am trying to take your VBA course but it is closed now. When will it be available?
Hi Nader, thank you for your interest to our VBA course. We now have the Elevate course which also includes the VBA. Here's the link for more info www.excelcampus.com/elevate-excel-invite/
I do have and use all three functions. No favorite. Tool choice depends on the problem at hand. They each have specialized applications. But maybe a fourth function TEXTBETWEEN!! It's important to recognize that all Dynamic Array functiond have the same incompatibility withe the calc engine in tables. So MS will need to provide major changes to tables or else develop an interface between Dynsmic Arrays and the table. Maybe not so easy since they need to keep the table calc engine working with older versions of Excel.
Great points Richard! I agree that TEXTBETWEEN would be a nice one to have.
Jon. I’m sure you get requests all the time! I appreciate your videos, they have helped me quite a bit over the years. But I am really stumped here. I am trying to create a data base of parts for an item my company produces. The spares of parts a labeled Good and broken essentially. I want to have a drop down that only returns the good parts in a list. Is this possible?
Nice video. However, I think it'll be great if you don't show us already written formulas but you write the formulas as we're watching. That way, it'll be easier to understand the logic behind the formulas.
Thanks for the suggestion Samuel!
How about addresses? I would like the street names all together to be able to make walk lists...
How will you split the following: Johannes Petrus Jan van der Merwe. First, second and third name and then the surname.
Hi John how are you , John I am unable to find the solution one of the excel problem on Date with Customer & order value...I have to multiple answers to multiple questions for eg : New customer order atleast in the month....Could you please share your ID I will share the file with you ....Please help me in finding the solution ....