in this video i'm gonna go over text split function in excel and cases
where you can actually use this so let's start with a basic example here so if i have this text
right here if i wanted to split this text to multiple columns using this
comma separator i could just point to this text comma and then use a delimiter
in codes as a comma like so close parentheses hit enter and that should split this using these
commas as you can see to four different columns and then i could take this and drag this formula
down and we're gonna get the results for each row so once we split it by a comma for example if you
look right here we have this comma and then we have the space and apple so right now our
second column is going to be this space and apple instead of just being apple so if you wanted to
remove those extra spaces you could go back here and add trim function around this whole thing
and that should eliminate those extra spaces you can see here how i have apple now more to the left
because we just removed that space compared to grapes and carrots now if i drag this formula
down it's also going to fix the rest for me now i want to note the fact that
when you use this function the results you get here are text so this 199 is not number 199 it's
text so all the column results you get here are actually text so if that's something you want to
take care of you could do that as well and what we could do here we could simply just go back
and use something like let function to store this result in a variable i'm going to call it
separated array you can call this variable anything you want
then we'll run this to get our results we can now take this separated array and if we take that
and add zero to it it will basically try to add zero and see if it's able to do so so if i close
parentheses right now and hit enter you'll see that what happens here for numbers and dates it's
able to actually do that calculation and it's not able to do that for these other text calculations
here so what we can do we can go back here and put this calculation inside of an if error function
and we can say let's try to do this and if there's an error let's just return the results
as they used to be so i'll just do separated array i'll close parentheses for this if error function
and at this point if i'd enter and drag it down you'll see that we get our results so we get
these as text this is a number this is a date just no formatting so if i go here and do short
date you'll see that we're able to actually now create valid dates numbers and text from splitting
our data so let's go over a few other examples let's say we have a list of names so a very basic
example here would be to again use something like text split and then point to this text and then
separate it with a space in quotes so the limiter right now is going to be the space in the middle
if i'd enter we'll see how we can split first and last names to their own separate columns
now that can get a little more complicated if we have to do it here so if i try to do text split
point to this and then split it by a space
and then i try to drag this down well because we have this middle initials in some of these cases
then we split it to three columns like so so what if we want to just get first and last name we
want to basically not have this middle initials in the middle so what we could do we could basically
grab a particular word from this results so for example if i take this and add columns function
around it hit enter and drag it down you'll see that what i get here is the number of columns
that are created after we split that by a space so for this one we have two words so we got two
column results then three then two then three so if we could use this and get only the first word
and this last word which is this 2 3 to whatever number that is that should give us first and last
names from this so now let's try to do so so i'm gonna go back here to just text split for a second
drag this down for the time being so for example if i wanted to get the first board only now
i could go here and add a function
choose columns this function and provide this array and then here after comma if i just do
one close parentheses and hit enter and drag it down it's gonna only give us the first word
now if i wanted to get the second word i can go here and do two and drag it down
and you can see we got now the second word now what i want to do i want to actually get
the first word and then what we can do we can do a comma and actually give it another column position
so if i do one and two then it's gonna give us this first and second word then i can go here
and do comma three but what i actually want i want this second one to be the last word so instead of
hard coding 2 i want to basically just grab the last word out of it and the way we can do that
we can grab this text split function again and instead of this 2 we can just do columns and paste
that text split function in it if you remember what this does it counts how many columns we have
and that number is always going to be the last column number so if i hit enter and drag this
down now we'll get this basically first and last word so if you only wanted the last word
you could just remove this one and comma and that should only give us the last word
from that particular text now i want the first one too so i'm gonna do one comma this now we can
make this a little more efficient by not repeating this same thing twice in our formula so i can go
before this and store this in a variable using let function so i can do let call this words comma and
i'm going to paste that function comma and then after that in this calculation instead of doing
this i can simply just use that variable words here and one more time words here too and that
way we only do this text split calculation once in our formula i do need another close parenthesis
to finish this let function so if i enter and drag it down it should give us exactly the same results
so finally let's look at the other couple of arguments that are available in text split
so if you look here we have this date comma then we have the words comma then number and
then there's a semicolon and it goes to the next date so this whole thing is a single line and
basically there is a semicolon that separates each record in this particular line and then
we have commas that separate the columns so you can say that this semicolon is the row separator
and this commas are columns separators i'm going to make this column smaller
so what i can do here i can use that text split and point to this data comma and then as a column
delimiter i can use comma and then comma again and as a row delimiter we can use a semicolon close
parenthesis and hit enter and you can see how we take this one cell with text and we split it like
this to multiple rows and columns based on those separators now you could also have situations when
your cells look like this so there's no like semi-colon here but there's a new line
separating these lines and you want to split this in the same fashion so i can go here and do text
split and again i'm going to point to this comma as a column we're going to use that
comma as a delimiter again comma again and as a row delimiter we're going to use a space
and the way we can do a space we'll do quotation and as a space i'm gonna do alt enter or option
enter if you're on a mac close quotation so now my separator here is this new line
and i'm gonna close parentheses and hit enter and you'll see how that works pretty much the same way
now you may also want to take care of those spaces before and after your words and make
sure that these are actually numbers and dates which currently aren't and you
can tell by just looking at the alignment see how everything is aligned to the left
and just to prove that these are not numbers i'm gonna go ahead and try to do something like sum
and you'll see that we get a zero so at this point if i go here and trim this
close parenthesis and hit enter it will remove those extra spaces
but i want to now convert these two numbers and these two dates
so again we can use that trick i did before so i'm going to store this in a variable i'll do let
data comma to be called this i'll go after that and do a comma
and then what we're going to do we're going to do if error and we'll try
to basically convert it to a number so we'll take that data and add 0 to it and if it fails
comma we'll just use data close parentheses close parentheses one more time for that let
function hit enter and now these are numbers and you can see how the sum started working
because these are actually numbers and if you want to convert this to a date
you can and now we have proper dates and proper numbers in our data finally i want to go over
those last couple of arguments we have here in text split and for that let's just go to a basic
split so as a column the limiter will use a comma and as a row delimiter will use that alt enter or
option enter to create a new line in quotes close parenthesis hit enter just to get back to this
now if i go back to this function right here see there's this ignore empty and this padding
arguments that we didn't use so first let's talk about that ignore empty so let's say in this
original text we have a new line here i'm going to do that option enter again to add a new line
so you can see how when i have that new line now we have another row here and then we have bunch of
nas because there's nothing here to split with a comma now you might want to ignore that empty line
and the way you can do so is by going here and adding a comma and when it says ignore empty cells
we can say true and if i enter you'll see that's gone so the default behavior is false which if you
don't do anything it's the same as doing false here so i can go back and do true so that's the
reason you may need that parameter if you decide you want to use that and then finally there's this
pad which is the last parameter now what that does in this particular example every row with
split ends up getting exactly the same number of columns but let's say i go here and i remove this
last couple of things here and we have just grapes and we have the date
but the rest is not there so if i hit enter
you'll see that these are gonna get n a's because there's nothing and this is where we can pad this
with some different values so whatever you use after comma here so if i just say hi
see it's gonna just basically fill those cells with that text now if you want them to be blanks
you can just do double quotes like so and there we are and those are your last two arguments here in
this text split function so hopefully that covers everything you need to know about this let me know
if you have any questions thanks for watching please subscribe and i'll see in the next video