14 NEW Excel Functions - TEXTSPLIT, Array Shaping, and more!

Microsoft just released 14 new text and array functions for Excel. Join me for this live training where I explain what the new functions are and how to use them in formulas. Click the Set Reminder button above to get notified when the event starts.

You will also be able to ask questions through the chat.

The most useful and popular functions will likely be TEXTSPLIT, TEXTBEFORE, and TEXTAFTER. I'll explain how they replace some existing formulas that require multiple nested functions and make life much easier!

The 14 new functions are:

1. TEXTSPLIT
2. TEXTBEFORE
3. TEXTAFTER
4. VSTACK
5. HSTACK
6. TOROW
7. TOCOL
8. WRAPROWS
9. WRAPCOLS
10. TAKE
11. DROP
12. EXPAND
13. CHOOSEROWS
14. CHOOSECOLS

Join me at 11:00AM Pacific Time on Friday to learn some new functions that will save time with your everyday Excel tasks.

Download the file I used during the event:

March 23, 2022 Update: The functions should now be available to EVERYONE that is on the Insiders Beta channel for Microsoft 365. The flighting can take a while to roll out and you might need to restart Excel to see get the updates.

[magdahassib]

Thanks for your perfect teaching, I am from Egypt

[anniesuem]

Thank you so much, Jon... TEXTSPLIT is awesome!!!😊

[wilmagabuat]

Thank you. good morning! watching here from Australia.

[Dime]

You have definitely found your calling🙂. You are an awesome teacher, thank you for making it easy to understand.

[FPerformancenumber]

The best teachers are the ones that make it easy to understand, thank you Jon, you explain the new formulas so its easy to follow and understand.

[jagatkr]

Great to see jon . i was waiting for this type of video. thanks jon.

[henryg]

Good to see you back, John. No wonder, given the length of this video. Great work.

[markmcclure]

An array is a list of values. This is great! Thank you.

[bhaskar]

Hi Jon, Thanks for being my excel Guru

[ireneogcanete]

Thank you sir for sharing your knowledge. Regards from the Philippines...

[wayneedmondson]

Thanks for the comprehensive demo Jon! Will definitely refer back to this when the new functions are released. Thumbs up!!

[TimEllisWW]

Watching from Sydney, AUS. Fantastic, this will speed up a lot of text manipulation. Yes, Need this NOW, thanks.

[oscarcastellanos]

thanks for your help this is very useful for me

[PaulEBrownbill]

excellent live video stream. More of them please if you can. I am glad you recorded it as I was an hour late, thought it started at 7pm.

[RInvestments]

Hi Jon, bite-sized videos for each function would be great!

[leifnorinder]

Good lesson! But are the new features only for 365?

[srider]

Bookmarks are desperately needed for this video.

[markpodesta]

Looking forward to using the new functions when they come out.

[peterbartholomew]

Yippee! The new functions just turned up this morning (Tuesday)!

The array is just the rectangular grid of numbers, addressable using row and column indices. A range has many more properties besides the values, such as row and column numbers relative to the sheet; as a reference it may be intersected with other ranges and can be tested by ISREF(). Also SUMIFS etc will not accept arrays, they must be range references.

Meanwhile, looking at your first example, I noticed that you cannot split the entire column in two with one formula. Only the first name comes through.
Workarounds included
= CHOOSE({1, 2}, TEXTBEFORE(list, " "), TEXTAFTER(list, " "))
= TEXTSPLIT(TEXTJOIN(";", , list), " ", ";")

or even, if hard pressed, the dreaded 'thunk'.

[mivanows]

I can't wait to start using it. I have created something similar but it is hard to send files to other people. Great job showing the new functions! :)