So Excel got this great new function called TEXTSPLIT. It takes care of a problem that we've all been having for years, that's split text.
Especially if it has multiple spaces, or it has multiple commas, or a mix of different
delimiters. So, the more splitting you needed, the bigger your problem became, and actually
the bigger your formula ended up being. That's all over now because TEXTSPLIT takes care of it all. You can even combine it with other functions to
make Excel do stuff that we could never do before. I guess we just want to see it, right? So
let's get to it. Let's start with the basics. So here, I'm
collecting different skills from different people, and everything is in a single cell. I want
it split into multiple cells. No problem for TEXTSPLIT. I'm just going to start typing
TEXTSPLIT, it's right here. I need my text, which is this one, and then I have to define my
delimiter, based on what I want to split these. Well, in this case, it's a comma, so I'm
going to put a comma in quotation marks, close the bracket, press Enter, and I get
everything split. But take a closer look at this, I have a comma and a space, so there is
an extra space that comes after these. Now you have different options to get rid of this
space. You could put this function inside the TRIM function and that's going to trim out
that space, or...just going to press "Ctrl + Z" to go back...you could adjust your delimiter
to be a [comma][space], and you end up with the same result. What's the problem when I copy
this down? Is that going to work everywhere? No, because some people used a semicolon as a
delimiter, and that ends up messing my results. Well, no problem for TEXTSPLIT because you can
add multiple delimiters in here. You just have to put it inside curly brackets. I'm going
to find my curly brackets on the keyboard, we need to close this as well. And inside, we are
going to define our multiple delimiters. I'm just going to add my separator and inside the quotation
marks, I'm going to add my second delimiter which, in this case is a [semicolon][space] and close the
quotation mark. You can add as many delimiters as you want. To be on the safe side, why not add
a colon as well? And press enter. Now, when I pull this down, everything is split properly. If
I happen to add a "[colon]Excel" here, it works properly. Next up, let's look at a case where we
have to split the name into first and last, but we don't want to get this "Mr" and "Mrs" in there.
We're going to use TEXTSPLIT. The text is this, and we have a space as a delimiter. Now, if I just
leave this as is and press Enter, I do end up with that "Mr" and "Miss" in there, but remember what
we saw before, I can add multiple delimiters. So, I could treat these "Mr" and "Miss" as their own
delimiters. So in quotation marks, I'm going to put "Mr," and let's add another one for "Miss"
and let's close that curly bracket. Now, when I press Enter, I get everything split, but I end up
with this empty cell here. I want to ignore empty cells. Well, that's not a problem because "Text
Split" has multiple arguments that are optional. We have a row delimiter, we don't need that right
now, but we need this one, ignore empty cells, we are going to put TRUE for that. And now, when
we press Enter, we have first and last names. In this case, we have our data written like
this in a single cell and we want to bring it into multiple cells that looks like this. No
problem for TEXTSPLIT. Let's start with TEXTSPLIT, our text is this. Now, we can make use
of the column delimiter and the row delimiter. So, what do we want the separator of the columns
to be? It should be the equal sign, so let's put that for our column delimiter. Next is the row
delimiter. That should be the comma. I'm going to go with "[comma][space]" and close the bracket,
press Enter, and I get everything spilled properly. Next up, let's do something really cool. We
want to take our skills here and we want to get them back in a single cell, but we want them
internally sorted. So this one should have "Excel" first, and then "Google Sheets," then "Power BI,"
"PowerPoint," and "Word" last. How do we do that? Well, we're going to use a few functions together
to get what we want. First, I'm going to start from the inside. We're going to use TEXTSPLIT
to split these up. That's our text. My delimiter here is a comma but here, I noticed I don't have
a space, whereas I have a [comma][space] there. So, to be on the safe side, I'm just going to go with
a comma as the delimiter, but I'm going to put this inside the TRIM function so that I don't have
any space at the beginning of the words. Now that I have everything split separately, I can use the
SORT function and get these sorted. My array is this. My sort index is just the one row that
I have, so I'm just going to skip that argument. The sort order default is ascending. I'm going
to skip that argument because I want the default. But this is something I don't want to skip, and
I can't skip because I am sorting by columns in this case, so I need a TRUE for that last
argument. I close the bracket, press Enter, and I have everything sorted. But I don't want them
on separate cells. I want to bring everything together again into a single cell. So, I'm going
to use the TEXTJOIN function and stitch these back together. My delimiter is a "[comma][space]"
in quotation marks. I can ignore empty cells, in case I have nothing or just the space between
the delimiters, and last is my text which is the formula I wrote before. Close the bracket, press
Enter and we have everything internally sorted. We get "Excel" first, then "Oracle," "Power BI," and
last is "SAP." Amazing functions that help us do amazing things. So, as you can see, this function
is like the formula version of the "Text to Columns" feature that we have in Excel, except
it's smarter and it's a function, so it's dynamic. What do you think about this? Are
you excited about this development? Let me know in the comments. As usual, thank
you for watching. Thank you for being here. Subscribe if you aren't subscribed, and
I'm going to see you in the next video.
