Fourteen new functions in Excel: TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, DROP, TAKE, EXPAND, TOCOL, TOROW, WRAPCOLS, WRAPROWS.
Links to Help topic for each:
TEXTSPLIT: TEXTSPLIT function
TEXTBEFORE: TEXTBEFORE function
TEXTAFTER: TEXTAFTER function
VSTACK: VSTACK function
HSTACK: HSTACK function
CHOOSECOLS: CHOOSECOLS function
CHOOSEROWS: CHOOSEROWS function
DROP: DROP function
TAKE: TAKE function
EXPAND: EXPAND Function
TOCOL: TOCOL function
TOROW: TOROW function
WRAPCOLS: WRAPCOLS function
WRAPROWS: WRAPROWS function
Table of Contents
(0:00) Overview of 14 functions
(1:00) Split text with TEXTSPLIT
(2:17) Join arrays into a column with TOCOL
(2:42) Stack arrays with HSTACK
(2:58) DROP the first row
(3:10) TEXTBEFORE and TEXTAFTER
(4:20) HSTACK and VSTACK
(4:35) DROP function
(5:29) TAKE Function
(6:20) CHOOSEROWS & CHOOSECOLS Functions
(7:10) TOCOL and TOROW functions
(8:20) Shuffle and deal a deck of cards
(9:10) WRAPCOLS function
(10:10) Will make Excel easier
(10:50) UNIQUE of a rectangular range
(11:05) TEXTSPLIT
(11:39) Flighted to part of Insiders
Links to Help topic for each:
TEXTSPLIT: TEXTSPLIT function
TEXTBEFORE: TEXTBEFORE function
TEXTAFTER: TEXTAFTER function
VSTACK: VSTACK function
HSTACK: HSTACK function
CHOOSECOLS: CHOOSECOLS function
CHOOSEROWS: CHOOSEROWS function
DROP: DROP function
TAKE: TAKE function
EXPAND: EXPAND Function
TOCOL: TOCOL function
TOROW: TOROW function
WRAPCOLS: WRAPCOLS function
WRAPROWS: WRAPROWS function
Table of Contents
(0:00) Overview of 14 functions
(1:00) Split text with TEXTSPLIT
(2:17) Join arrays into a column with TOCOL
(2:42) Stack arrays with HSTACK
(2:58) DROP the first row
(3:10) TEXTBEFORE and TEXTAFTER
(4:20) HSTACK and VSTACK
(4:35) DROP function
(5:29) TAKE Function
(6:20) CHOOSEROWS & CHOOSECOLS Functions
(7:10) TOCOL and TOROW functions
(8:20) Shuffle and deal a deck of cards
(9:10) WRAPCOLS function
(10:10) Will make Excel easier
(10:50) UNIQUE of a rectangular range
(11:05) TEXTSPLIT
(11:39) Flighted to part of Insiders
Transcript of the video:
It's like Christmas. 14 new functions in Excel.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
This is episode 2469.
I've been waiting for some of these functions for quite sometime.
I'm really happy that they finally have made it an Insider's Beta today.
14 functions, three of them are text functions, TEXTSPLIT, the opposite of TEXTJOIN, is a function that would have made so many episodes in my YouTube channel so much easier. TEXTBEFORE and TEXTAFTER.
Two array stacking functions, VSTACK and HSTACK.
And frankly, if you had Charles Williams’ add-in, you've already had these for a while.
Five functions for selecting from an array, CHOOSECOLS, CHOOSEROWS, DROP, TAKE, and EXPAND.
And then these are awesome, four array shaping functions that takes values and puts them in a single column, single row, or then wraps it back into a rectangular range. Let's take a look at each of these.
All right, so let's start with TEXTSPLIT.
It like text to columns, but in a formula, you start with input text, what to break it into columns with, what to break it into rows with.
Do we ignore?
So if there's two commas in a row or two spaces in a row, do we ignore the empty one? And then should we pad_with?
And I'll show that. So here's some really long text.
It's the transcript of one of my videos. And if I say TEXTSPLIT comma comma.
So in other words, I'm not going to break it into columns.
I just want to break it at rows and break it at every period so I get one row per sentence, except for...
I'm always famous for saying “something, right, question mark. Right? I just did it. Okay.
So, we want to break it at the period or the question mark.
So you're allowed to, here in either delimiter, specify an array constant.
So I'm breaking it at the period or a question mark.
That gets me an extra sentence because of that question mark.
All right.
Now, then let's take that and split it both at a space or a comma-space.
So we get individual words going across, which is awesome.
And then from here, it would be easy to use the new to column function, TOCOL, to break that into one huge massive column, and then get the unique list of words, get a count of each of those. So COUNTIFS, count all of these words.
Here that are the unique words.
So for each word that I said, how many times did I say it?
It'd be nice if I could put those back together into a single array. This was something we could never do before.
So the new HSTACK function, stack this array and this array, and I get a single array.
Sort that whole thing. So the SORT, we've had forever, right?
And I was surprised, and then I ended up with a bunch of empty cells, right?
183 empty cells. I don't want that.
So the new DROP function says to drop the first row, and I now have a sorted frequency distribution of words that I say a lot.
TEXTBEFORE, we give it some input text, and then we want everything before a certain character.
If we want to look for the nth character and ignore case.
So in the Excel help file for this, we have little red riding hood's red hood.
If we're looking for red capitalized and we want everything before that, so TEXTBEFORE.
Everything before Red we get little.
But if we search for everything before red, lowercase red, we get everything up to that.
And if we don't care, then there's an optional fourth argument there that says ignore case. Ignore case.
If we want to look for everything before the second red, case insensitive, then we could use that we want the second instance num and ignore case equals True.
The place where I can really see this is going to happen a lot is if you have part numbers with a dash in the middle, you want everything before the dash or after the dash.
And you could always do this with FIND. Equal LEFT of FIND, but now it seems to be easier.
Just get me everything before the dash, or get me everything after the dash.
HSTACK and VSTACK.
HSTACK will take a bunch of arrays and arrange them horizontally.
VSTACK will take a bunch of arrays and arrange them vertically.
Okay.
The DROP function, this is a very specific function.
It will remove rows or column from the beginning or end of an array. So we have a range up here.
If we want to remove the top row and the left column, just keeping the numbers, we ask for the DROP and we want to remove the first row and the first column.
If we would want to remove the totals from the end, this is really cool, you can specify negative one and it'll count from the bottom or count from the right.
So we want to remove headings and totals.
The DROP of the DROPs.
So first, remove the first row and first column, then the last row and the last column. And we end up with just the numbers.
The opposite of DROP is TAKE. TAKE will keep certain rows or columns.
So here's our original array.
If I wanted just the names and totals, if I wanted just the names from this, it's TAKE the range comma comma one.
If I wanted just the totals from the end, it's equal TAKE, and again the negative one will count from the right hand side.
And to put those together into a single array, then HSTACK the two TAKES. Same concept here.
TAKE can take the first row so we get the product names.
And it can also take the last row so we get the totals.
And then VSTACK that into a single array.
DROP and TAKE are very specific that they only operate on the edge rows, so either the columns from the right or the columns from the left, or the rows of the top or the rows at the bottom.
It seems to me like CHOOSEROWS is going to be much more useful, because we can specify any number of items. So here, doing the same thing, CHOOSEROWS.
I want the first and the last.
Over here, we have a bad data set where they have quarter, value, quarter, value, quarter, value. And I want to get just the even numbered columns.
We could do that with CHOOSECOLS. And which ones do I want?
I want one row, four columns, starting at two, jumping by two to get column 2, 4, 6, 8.
Down here then, equal “Q” and SEQUENCE( of 1 comma 4 gives me Q1 through Q4, and then I can VSTACK the headings and the data back in to essentially do what I could do in Power Query, but do it with a formula.
TOCOLumn and TOROW: this takes an array, a rectangular array, and we can unwind everything. This range and change it into a column.
And you see that by default, it goes across, history, math, history, math, history, history, math, history, math, history.
If we would want to do a column by column, there's an optional argument out here called scan by column, and that'll do history, composition, lunch, physics, shop, math, econ, lunch, and tech. See that we're getting the blank cell there.
There's an optional argument. Don't ignore anything or ignore blanks.
So if we want to unwind and ignore blanks, we add that extra one at the end.
And then again, the UNIQUE has been around forever.
So we get a UNIQUE list of that rectangular range.
That was an entire podcast episode and it was very difficult to do. And now, it is super easy.
TOROW is the same thing, but instead it's creating a row instead of having to wrap TOCOL into a TRANSPOSE.
There was another old episode of the podcast where I was trying to shuffle and then deal a deck of cards.
This is now super easy thanks to the TOCOL and WRAPCOL.
So right here, I have Ace through King down the left hand side, the four card suits across the top, and then a little single formula to return all 52 cards.
To generate those into one single column, I point to that range.
Actually, why am I not just point it to B5 hash there?
That would be much simpler. Perfect.
And then to shuffle the cards, it's the SORTBY, and I'm going to sort by a random array of 52.
So now every time that we calculate, we get a brand new ordering of the deck.
And then to deal those out, let's deal it to 4 players.
I'm using the WRAPCOLs here. What's the vector?
So the vector is the sorted deck. And what are we wrapping by?
We're wrapping into 4.
So the 10, 4, 6, 6 gets dealt to player one, player two, player three, player four.
And then we start with the fifth card, goes up here, and so on.
If we have an uneven number, you're going to get #N/A’s out here, unless you use pad_with.
So pad_with, I have quote quote there to make sure that I'm not getting #N/As at the end.
You could also kind of wrap this the other direction with WRAPROWS.
So, that would give the first card, and then the second card, third card, fourth card, fifth card, and then go to the next player, and so on. Like that.
There were so many times with dynamic arrays that I was trying to do something and it would, you're trying to join the totals onto the end of array or take the totals off the end of an array.
And these TAKE, DROP, CHOOSECOL, CHOOSEROWS, HSTACK, VSTACK, those are all going to make dynamic arrays so much easier.
The kind of edge cases where I would run into a problem.
And then I would have to just go back to regular formulas because I couldn't do it all with a single array.
Now I'm going to be able to create arrays kind of on the fly.
Or if I end up with a filtered data set that has headings or doesn't have headings, I can take those headings off or the totals off, or whatever I might need.
And then just the UNIQUE of a rectangular range, now being able to use the TOCOL, and then send TOCOL into UNIQUE is going to be super helpful.
And then TEXTSPLIT, I don't know how many times on the Netcast, and it was so easy - It was a three line UDF that Brad Yundt had contributed.
We finally have it.
Although, it's better now because it's not just separating it into columns, but also the ability to split it into rows and to deal with multiple delimiters and not being able to pad or ignore empty.
Of course, initially, this'll be flighted to 25 or 50% of Insiders.
You might check Excel online, it probably has a better shot of having this than if you're not in the first flighting.
Two weeks out, if everything goes well, then it will roll to the rest of the Insider Beta group.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Now, if you love Excel, check out my new courses on the Retrieve platform.
They're video courses, but you just type what you're looking for, it takes you right to that spot in the video, and there's a complete transcript in several languages. It's a super fast way to learn.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
This is episode 2469.
I've been waiting for some of these functions for quite sometime.
I'm really happy that they finally have made it an Insider's Beta today.
14 functions, three of them are text functions, TEXTSPLIT, the opposite of TEXTJOIN, is a function that would have made so many episodes in my YouTube channel so much easier. TEXTBEFORE and TEXTAFTER.
Two array stacking functions, VSTACK and HSTACK.
And frankly, if you had Charles Williams’ add-in, you've already had these for a while.
Five functions for selecting from an array, CHOOSECOLS, CHOOSEROWS, DROP, TAKE, and EXPAND.
And then these are awesome, four array shaping functions that takes values and puts them in a single column, single row, or then wraps it back into a rectangular range. Let's take a look at each of these.
All right, so let's start with TEXTSPLIT.
It like text to columns, but in a formula, you start with input text, what to break it into columns with, what to break it into rows with.
Do we ignore?
So if there's two commas in a row or two spaces in a row, do we ignore the empty one? And then should we pad_with?
And I'll show that. So here's some really long text.
It's the transcript of one of my videos. And if I say TEXTSPLIT comma comma.
So in other words, I'm not going to break it into columns.
I just want to break it at rows and break it at every period so I get one row per sentence, except for...
I'm always famous for saying “something, right, question mark. Right? I just did it. Okay.
So, we want to break it at the period or the question mark.
So you're allowed to, here in either delimiter, specify an array constant.
So I'm breaking it at the period or a question mark.
That gets me an extra sentence because of that question mark.
All right.
Now, then let's take that and split it both at a space or a comma-space.
So we get individual words going across, which is awesome.
And then from here, it would be easy to use the new to column function, TOCOL, to break that into one huge massive column, and then get the unique list of words, get a count of each of those. So COUNTIFS, count all of these words.
Here that are the unique words.
So for each word that I said, how many times did I say it?
It'd be nice if I could put those back together into a single array. This was something we could never do before.
So the new HSTACK function, stack this array and this array, and I get a single array.
Sort that whole thing. So the SORT, we've had forever, right?
And I was surprised, and then I ended up with a bunch of empty cells, right?
183 empty cells. I don't want that.
So the new DROP function says to drop the first row, and I now have a sorted frequency distribution of words that I say a lot.
TEXTBEFORE, we give it some input text, and then we want everything before a certain character.
If we want to look for the nth character and ignore case.
So in the Excel help file for this, we have little red riding hood's red hood.
If we're looking for red capitalized and we want everything before that, so TEXTBEFORE.
Everything before Red we get little.
But if we search for everything before red, lowercase red, we get everything up to that.
And if we don't care, then there's an optional fourth argument there that says ignore case. Ignore case.
If we want to look for everything before the second red, case insensitive, then we could use that we want the second instance num and ignore case equals True.
The place where I can really see this is going to happen a lot is if you have part numbers with a dash in the middle, you want everything before the dash or after the dash.
And you could always do this with FIND. Equal LEFT of FIND, but now it seems to be easier.
Just get me everything before the dash, or get me everything after the dash.
HSTACK and VSTACK.
HSTACK will take a bunch of arrays and arrange them horizontally.
VSTACK will take a bunch of arrays and arrange them vertically.
Okay.
The DROP function, this is a very specific function.
It will remove rows or column from the beginning or end of an array. So we have a range up here.
If we want to remove the top row and the left column, just keeping the numbers, we ask for the DROP and we want to remove the first row and the first column.
If we would want to remove the totals from the end, this is really cool, you can specify negative one and it'll count from the bottom or count from the right.
So we want to remove headings and totals.
The DROP of the DROPs.
So first, remove the first row and first column, then the last row and the last column. And we end up with just the numbers.
The opposite of DROP is TAKE. TAKE will keep certain rows or columns.
So here's our original array.
If I wanted just the names and totals, if I wanted just the names from this, it's TAKE the range comma comma one.
If I wanted just the totals from the end, it's equal TAKE, and again the negative one will count from the right hand side.
And to put those together into a single array, then HSTACK the two TAKES. Same concept here.
TAKE can take the first row so we get the product names.
And it can also take the last row so we get the totals.
And then VSTACK that into a single array.
DROP and TAKE are very specific that they only operate on the edge rows, so either the columns from the right or the columns from the left, or the rows of the top or the rows at the bottom.
It seems to me like CHOOSEROWS is going to be much more useful, because we can specify any number of items. So here, doing the same thing, CHOOSEROWS.
I want the first and the last.
Over here, we have a bad data set where they have quarter, value, quarter, value, quarter, value. And I want to get just the even numbered columns.
We could do that with CHOOSECOLS. And which ones do I want?
I want one row, four columns, starting at two, jumping by two to get column 2, 4, 6, 8.
Down here then, equal “Q” and SEQUENCE( of 1 comma 4 gives me Q1 through Q4, and then I can VSTACK the headings and the data back in to essentially do what I could do in Power Query, but do it with a formula.
TOCOLumn and TOROW: this takes an array, a rectangular array, and we can unwind everything. This range and change it into a column.
And you see that by default, it goes across, history, math, history, math, history, history, math, history, math, history.
If we would want to do a column by column, there's an optional argument out here called scan by column, and that'll do history, composition, lunch, physics, shop, math, econ, lunch, and tech. See that we're getting the blank cell there.
There's an optional argument. Don't ignore anything or ignore blanks.
So if we want to unwind and ignore blanks, we add that extra one at the end.
And then again, the UNIQUE has been around forever.
So we get a UNIQUE list of that rectangular range.
That was an entire podcast episode and it was very difficult to do. And now, it is super easy.
TOROW is the same thing, but instead it's creating a row instead of having to wrap TOCOL into a TRANSPOSE.
There was another old episode of the podcast where I was trying to shuffle and then deal a deck of cards.
This is now super easy thanks to the TOCOL and WRAPCOL.
So right here, I have Ace through King down the left hand side, the four card suits across the top, and then a little single formula to return all 52 cards.
To generate those into one single column, I point to that range.
Actually, why am I not just point it to B5 hash there?
That would be much simpler. Perfect.
And then to shuffle the cards, it's the SORTBY, and I'm going to sort by a random array of 52.
So now every time that we calculate, we get a brand new ordering of the deck.
And then to deal those out, let's deal it to 4 players.
I'm using the WRAPCOLs here. What's the vector?
So the vector is the sorted deck. And what are we wrapping by?
We're wrapping into 4.
So the 10, 4, 6, 6 gets dealt to player one, player two, player three, player four.
And then we start with the fifth card, goes up here, and so on.
If we have an uneven number, you're going to get #N/A’s out here, unless you use pad_with.
So pad_with, I have quote quote there to make sure that I'm not getting #N/As at the end.
You could also kind of wrap this the other direction with WRAPROWS.
So, that would give the first card, and then the second card, third card, fourth card, fifth card, and then go to the next player, and so on. Like that.
There were so many times with dynamic arrays that I was trying to do something and it would, you're trying to join the totals onto the end of array or take the totals off the end of an array.
And these TAKE, DROP, CHOOSECOL, CHOOSEROWS, HSTACK, VSTACK, those are all going to make dynamic arrays so much easier.
The kind of edge cases where I would run into a problem.
And then I would have to just go back to regular formulas because I couldn't do it all with a single array.
Now I'm going to be able to create arrays kind of on the fly.
Or if I end up with a filtered data set that has headings or doesn't have headings, I can take those headings off or the totals off, or whatever I might need.
And then just the UNIQUE of a rectangular range, now being able to use the TOCOL, and then send TOCOL into UNIQUE is going to be super helpful.
And then TEXTSPLIT, I don't know how many times on the Netcast, and it was so easy - It was a three line UDF that Brad Yundt had contributed.
We finally have it.
Although, it's better now because it's not just separating it into columns, but also the ability to split it into rows and to deal with multiple delimiters and not being able to pad or ignore empty.
Of course, initially, this'll be flighted to 25 or 50% of Insiders.
You might check Excel online, it probably has a better shot of having this than if you're not in the first flighting.
Two weeks out, if everything goes well, then it will roll to the rest of the Insider Beta group.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Now, if you love Excel, check out my new courses on the Retrieve platform.
They're video courses, but you just type what you're looking for, it takes you right to that spot in the video, and there's a complete transcript in several languages. It's a super fast way to learn.
