From the May 2023 issue of Strategic Finance magazine, how to use TEXTSPLIT in Excel.
Transcript of the video:
What was going to be a simple little introduction to TEXTSPLIT.
Turns into an all-out journey through 10 functions. Including seven that are new. Hang on for the ride.
In this episode, TEXTSPLIT as values.
Hey, I want to welcome all the readers of Strategic Finance Magazine as well as my regular viewers.
In the May issue of the magazine, we're talking about the new TEXTSPLIT function that lets us essentially do text to columns in a formula.
Very simple, here's the text we want to split.
The delimiter is the pipe in this case, and it breaks it out into 13 columns. Isn't that awesome?
But then, I said, it's also possible not just to use the column delimiter, but to use a row delimiter as well.
So here's a contrived report, this is a horrible cell. It's an entire Word document in one cell.
But we managed to use TEXTSPLIT, breaking the columns at the colon and breaking the rows at the line feed.
Character 10 is when someone presses Alt+Enter in Excel, you get the line feed character.
All right, so generated that.
And in the magazine we talked about how you would have to use pad width to avoid these N/As here. And also how the function is correctly interpreting that colon.
Colon for Ed as an empty cell. You could turn that off with "ignore empty" being equal to true.
Match mode doesn't occur in this example because we don't have uppercase colons and lowercase colons.
If your delimiter was the letter A, you could choose whether we're going to match capital A or lowercase A by changing the match mode. Then let's go a little bit further.
Another new function that came out on the same day as TEXTSPLIT is this great new CHOOSECOLS.
So we have that great formula that's working on the inside that's giving us all of the months.
And now quarters that I've added in and I want to get just the first column, that's the name.
And then Q1, which is the fifth column, the ninth column, the 13th column, and the 17th column.
So you see there with CHOOSECOLS. I'm actually specifying the entire list of which columns I want.
So now I can grab data from here. Break it out into columns and get just the quarterly totals.
But then let's go even better.
The SEQUENCE function, which is a little bit older, it dates back to 2019. I want five rows, one column, starting at one.
Jumping by four, and that generates the numbers 1, 5, 9, 13, 17, which is what I've hard coded out here.
So I don't have to hard code it out there.
I can just put the SEQUENCE function and that little bit of formula fragment will generate 1, 5, 9, 13, 17. And I get the exact same report.
It didn't make it into the magazine, but I plan to talk about using the TAKE function.
The TAKE function will take either the first rows or first columns or last rows or last columns.
And so, here, by using the month of today, because it's April when I'm recording this, I'd get the number four.
I actually have to add one for the names and then I added two to make it appear as May for the magazine and that generates a Year to Date report.
And next month, when I open this, I'll get the next month, I'll get June, July and August and so on. Right? Really cool.
I closed the article in the magazine with this last example that really kind of does it all.
It has CHOOSECOLS to get the quarters.
I handle these extra sales here for Hank with zeros, hard code the report. And I was super happy with all of that.
But then I realized the huge problem that we have here is that these numbers are not numbers. They're text.
And if we look down here to get the sum, the sum is zero.
So I'm at the end of the article and I'm like, "Oh no, we should deal with this".
So I invited you to come out here to see, if you really needed these to be numeric, how we could manage to do that. All right. The first thing we're going to do.
We have this formula that's working perfectly.
I'm going to use that formula over and over and over again down here. So we have the TAKE function.
The TAKE function will give me just the first row, that's the, "Which row?" piece of this whole thing. And that gets me just the headings.
That's step one. Step two is I want to isolate the names along the left-hand side.
So the TAKE, this time, comma, comma, one. That'll give me just the first column.
But my problem is that that gave me the word Name at the top, and I don't need the word Name because I already have the word name from the first array. So then I had a DROP.
DROP is sort of like TAKE, you can drop, in this case, one row from the top. So that gets me all of the names without the heading of Name. All right, good.
And now here's the magic part.
We managed to take all of the columns, drop the first row in the first column, which gets me just the numbers.
And once I have just the numbers, then it's easy to coerce those from text into values by simply adding zero.
So there's my report, headings across the top, names down the side, and true numbers that will actually add up in the report.
It's not that bad, but wouldn't it be nice to put all this back together?
Yes, yes. It would definitely be nice. So we have three arrays here.
The green array, the orange array, the yellow array. And what I'm going to do is, I'm going to VSTACK.
I'm going to VSTACK the green array, and underneath that I'm going to stack the HSTACK.
V stands for vertical stack. H stands for horizontal stack.
So I'm going to HSTACK the orange array and the yellow array.
That means put them, these two things that are side by side, into a single array, and then we end up with one formula that gives me all of the answers.
And those answers are still numeric and we have a total down here. Beautiful.
So then, putting it all back together, here is the one formula that's doing the VSTACK of the TAKE, that's getting the names across the top, the HSTACK of the DROP, of the TAKE, of the CHOOSECOLS.
That's getting the name along the left. And then the DROP of the CHOOSECOLS plus zero to get all of the numbers.
This all made sense when I started down this path, and you could see how it just builds from one step to another step to another step, but it ends up at a formula that is very, very hard to explain.
Now, for those of you reading the magazine, I want you to take a look. Go to YouTube.
Down in the YouTube comments below this video, where you'll find all kinds of other great alternatives for doing this, such as Power Query or LET functions or LAMBDA functions. Well, hey, thanks for stopping by.
We'll see you next time for another net cast from MrExcel.
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.
Turns into an all-out journey through 10 functions. Including seven that are new. Hang on for the ride.
In this episode, TEXTSPLIT as values.
Hey, I want to welcome all the readers of Strategic Finance Magazine as well as my regular viewers.
In the May issue of the magazine, we're talking about the new TEXTSPLIT function that lets us essentially do text to columns in a formula.
Very simple, here's the text we want to split.
The delimiter is the pipe in this case, and it breaks it out into 13 columns. Isn't that awesome?
But then, I said, it's also possible not just to use the column delimiter, but to use a row delimiter as well.
So here's a contrived report, this is a horrible cell. It's an entire Word document in one cell.
But we managed to use TEXTSPLIT, breaking the columns at the colon and breaking the rows at the line feed.
Character 10 is when someone presses Alt+Enter in Excel, you get the line feed character.
All right, so generated that.
And in the magazine we talked about how you would have to use pad width to avoid these N/As here. And also how the function is correctly interpreting that colon.
Colon for Ed as an empty cell. You could turn that off with "ignore empty" being equal to true.
Match mode doesn't occur in this example because we don't have uppercase colons and lowercase colons.
If your delimiter was the letter A, you could choose whether we're going to match capital A or lowercase A by changing the match mode. Then let's go a little bit further.
Another new function that came out on the same day as TEXTSPLIT is this great new CHOOSECOLS.
So we have that great formula that's working on the inside that's giving us all of the months.
And now quarters that I've added in and I want to get just the first column, that's the name.
And then Q1, which is the fifth column, the ninth column, the 13th column, and the 17th column.
So you see there with CHOOSECOLS. I'm actually specifying the entire list of which columns I want.
So now I can grab data from here. Break it out into columns and get just the quarterly totals.
But then let's go even better.
The SEQUENCE function, which is a little bit older, it dates back to 2019. I want five rows, one column, starting at one.
Jumping by four, and that generates the numbers 1, 5, 9, 13, 17, which is what I've hard coded out here.
So I don't have to hard code it out there.
I can just put the SEQUENCE function and that little bit of formula fragment will generate 1, 5, 9, 13, 17. And I get the exact same report.
It didn't make it into the magazine, but I plan to talk about using the TAKE function.
The TAKE function will take either the first rows or first columns or last rows or last columns.
And so, here, by using the month of today, because it's April when I'm recording this, I'd get the number four.
I actually have to add one for the names and then I added two to make it appear as May for the magazine and that generates a Year to Date report.
And next month, when I open this, I'll get the next month, I'll get June, July and August and so on. Right? Really cool.
I closed the article in the magazine with this last example that really kind of does it all.
It has CHOOSECOLS to get the quarters.
I handle these extra sales here for Hank with zeros, hard code the report. And I was super happy with all of that.
But then I realized the huge problem that we have here is that these numbers are not numbers. They're text.
And if we look down here to get the sum, the sum is zero.
So I'm at the end of the article and I'm like, "Oh no, we should deal with this".
So I invited you to come out here to see, if you really needed these to be numeric, how we could manage to do that. All right. The first thing we're going to do.
We have this formula that's working perfectly.
I'm going to use that formula over and over and over again down here. So we have the TAKE function.
The TAKE function will give me just the first row, that's the, "Which row?" piece of this whole thing. And that gets me just the headings.
That's step one. Step two is I want to isolate the names along the left-hand side.
So the TAKE, this time, comma, comma, one. That'll give me just the first column.
But my problem is that that gave me the word Name at the top, and I don't need the word Name because I already have the word name from the first array. So then I had a DROP.
DROP is sort of like TAKE, you can drop, in this case, one row from the top. So that gets me all of the names without the heading of Name. All right, good.
And now here's the magic part.
We managed to take all of the columns, drop the first row in the first column, which gets me just the numbers.
And once I have just the numbers, then it's easy to coerce those from text into values by simply adding zero.
So there's my report, headings across the top, names down the side, and true numbers that will actually add up in the report.
It's not that bad, but wouldn't it be nice to put all this back together?
Yes, yes. It would definitely be nice. So we have three arrays here.
The green array, the orange array, the yellow array. And what I'm going to do is, I'm going to VSTACK.
I'm going to VSTACK the green array, and underneath that I'm going to stack the HSTACK.
V stands for vertical stack. H stands for horizontal stack.
So I'm going to HSTACK the orange array and the yellow array.
That means put them, these two things that are side by side, into a single array, and then we end up with one formula that gives me all of the answers.
And those answers are still numeric and we have a total down here. Beautiful.
So then, putting it all back together, here is the one formula that's doing the VSTACK of the TAKE, that's getting the names across the top, the HSTACK of the DROP, of the TAKE, of the CHOOSECOLS.
That's getting the name along the left. And then the DROP of the CHOOSECOLS plus zero to get all of the numbers.
This all made sense when I started down this path, and you could see how it just builds from one step to another step to another step, but it ends up at a formula that is very, very hard to explain.
Now, for those of you reading the magazine, I want you to take a look. Go to YouTube.
Down in the YouTube comments below this video, where you'll find all kinds of other great alternatives for doing this, such as Power Query or LET functions or LAMBDA functions. Well, hey, thanks for stopping by.
We'll see you next time for another net cast from MrExcel.
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.
