hi my name is jamie and welcome to teachers tech today i want to show you how you can split cells
in microsoft excel so what i mean is let's say you have a example like this first name last name and
in one cell and you want to split them to put them in the individual cells like this i want to show
you four different ways that you can do this today in microsoft excel so let's get started right away
this video is following up a video i did on how to combine cells in microsoft's
excel so i showed six different ways how you can do that i'll put the link to that video
down below in the description and up above in the card and if you're looking for this worksheet that
i'm working on right now i'll put a link to that down below in the description as well and you can
follow along with the techniques i show you here today if you look at the different tabs at the
bottom of the worksheet you'll see the different topics to how i'm going to split the cells the
first one is flash fill very easy to use and then the next one is going to be the built-in feature
text to column in microsoft excel and make sure you're taking advantage of that one then i want
to show you how you can build a formula to split your cells in microsoft excel and then lastly i'm
going to show you how to do this with power query now everything's going to be time stamped in this
video so you can just look in the description and jump to different parts of the video
to get to that one way i'm showing you let's get started with flash fill first
so flash fill was introduced in excel 2013 and it's been in all the versions since then now to
use this it's very quick and easy and it finds a pattern and it'll kind of happen automatically
maybe if you notice it's happening if i go ahead and if i want to separate ned stark into two
different columns if i start just typing the word ned here and then i go to the next one now flash
phil is going to start to see a pattern and it's going to suggest a pattern so if i start typing
circe and you can see now i didn't even finish it but it's suggesting hey we found a pattern here
and it's grayed out kind of below do i want to accept this i can hit enter and you notice
there's the lot the flash fill symbol right here so if you click on this you can where you can see
undo and everything where i could have accepted it even without hitting the enter the other way
you can find flash fill now if i do the end part here and i just type stark in this one and i go to
the next one if i take a look up at the data here notice that flash fill is right here since since
i've typed in the one thing i can click on flash fill and it quickly kind of just filled it in and
you can see now i have my first name i have my last name separated into columns now i do want
to point out if all sudden something changed in this and so if i was going to go and change it to
just as an example i'll put an a here notice it doesn't update over here so i'd have to redo these
but that's using flash fill very easy to use and you can use flash fill even to combine cells like
i mentioned uh that in the video that i did about combining cells let's move on to the next feature
and that's using the uh text to column feature another easy feature to use is the text to column
feature built right into microsoft excel and where you're going to find this is go up to the data tab
and when we're looking in the ribbon look over here we can see text to column now what i want to
do is highlight what i want to split and go ahead and click on text to column so when i'm going to
click on that you get a couple different options for our purposes i'm going to choose the first one
the other one is more or less if everything was lined up perfectly and the space is always in the
same spot but we're gonna use the delimited and i'm just gonna go ahead and hit next now in our
case we're using a we have a space separator but if you had a comma in between you would
check comma or semicolon whatever one pertains to your uh example and i'm gonna treat consecutive
delimiters as one so if i had the comma and then a space there which would probably be happen
happening that's where it's going to help with the split but it's showing me the preview
right down here i'm going to go ahead and put next and at this point i'll just leave it as general
it's showing me the preview down here where do i want it to go well it's just going to move it it's
going to actually take you're not going to see this first column anymore and then it's just going
to break it into the two different columns here so i'm just going to go ahead and hit finish and
you see it gets quickly done and that's using the text to column feature that's right under the data
tab in microsoft excel so my next way to split cells is using a function or a form
and building a formula out of it and this is probably the most complicated way that will take
a little bit more time but if you go through the process of it it does have some benefits of using
a function or formula now i just want to point out before i get started in building i kind of want to
show you the finished one and explain what's happening and i'll leave this example in the
data sheet too so you can go ahead and try to copy it down and everything and see if you get it right
but if we look up here in the uh formula we have a couple different functions in this first one
so i have the left function what the left function does it will start from the beginning of the
left side of the cell and then i'm telling it to search for something inside of this and what am i
telling it to search for i'm telling it to search for a space so it finds that space but then i
want to add in minus one because the minus one i don't want the space included i just want it to be
one less than it so that's how i get the first one uh for this one now if i click on start on the
last one you can see now i'm looking at it from the right so i'm using the right function in this
and here is another function that i'm going to be using and this is the length so this tells
me how many characters are in this cell so it will look at this cell over here and then to
just would add up how many text characters are in there and then i'm telling it to do the minus
search what i used before uh and what is it looking for the space and at this time i
you can see it's still referring to the a2 but i don't have the minus one anymore because
i don't need the minus one there because it's just where that would start so those are two
different ones i want to try to explain as i go ahead and create them now all right let's start
building this out with the first name i've deleted the formulas that i was just showing you here
it's a formula so we need to start with our equal sign and the first function i'm going to start
with is the left one you can see it started to come up here i'm going to choose this one now
if i was just going to use the left function and i'll show you what it does by itself if i go ahead
it's asking me what text what cell click in here add the comment notice it's saying number kick
number of characters so if i was gonna say if i put in seven and i just ended this and hit return
what it did is one two three four five six seven it just it gave me that many if i copied it down
that's what it would give me the first seven characters so that doesn't work what i want
so i'm going to go back to this one here i'm just going to double click in here and start to
edit this now this is where it's important where we have to add the other function because this is
the search one where we're going to be looking for the space between the name so i'm going to put in
search you can see it comes up there and i'm going to start to fill in what it's asking for
so what am i looking for well i'm looking for a space how do i enter into search for a space
i need to put a quotation mark actually put a space in and then another quotation mark
and then a comma just like this so now i go back to the click the cell so within this
right here a2 i'm gonna end this with a bracket and this is where i need to put
the minus 1 because if i don't put the minus 1 it will actually return with the space and
not that you'll see this space but i don't want the space in there so this is why i'm going to put
minus 1 like this and end it so i'm going to hit enter and i have ned and i can just copy
this down into the different cells so let's now start with the last name
so you might think well why don't we take this and copy this and go to uh go to this cell and just
paste it in and change this to right and if i do that you can see it doesn't work so there's some
if i go ahead and copy this down definitely doesn't work so we need to make some changes
to this and this is how we go about it all right so i'm going to start with my equal sign again and
this time we are going to use our right function i'm going to select this it is going to be just
this a2 that i'm going to select on and notice it comes up with a number of characters here and
it's just like the first example i just showed you are with the left i will just return so many from
there this is where i need to put another function in and the function is going to be the len
function and i'm just going to if you can see it come up here right here so this is going to count
the number of characters in this cell here so i'm just going to click on this and this is where i'm
going to put another kind of a formula inside of this i need to actually minus the search for this
so minus what was i looking for well it was that space and i'm going to go ahead and just type in
search comes up right here and this is where i go and have to tell it what i'm searching for so
find the text and this is going to be using the quotation mark this space the quotation mark
and now i'm going to put my comma in and i'm also referring just to this cell so that keeps it
simple just remember when it's asking for the cell just go back to that cell and i can go ahead and
end this and i do need to put another bracket at the end because they always have to match right so
this one matched this one i'm going to put another one and hit enter and then i get the last name
here and i can go ahead copy this down and this time it works so again this can be a little bit
more complicated using the formulas but the nice thing with it is if something changed in here so
uh as an example if i type this s and hit return it's automatically updates and the other way uh
whether it be the flash fill or the text to column uh those don't automatically update
you'd have to redo the whole thing so even if you uh you know if you take a little bit more
time to create this formula this is definitely a way you can split cells in microsoft excel
so my last example today is using power query to split cells in microsoft excel and you might
think this might be a complicated one but it's not really to use it as the purpose
for just letting cells and you can combine cells also and i talked about that in my other video
but if i go i'm on the forest tab and i'm just going to go to my data tab right up here and
what i need to do is put from table or range and what it's going to do with this information
this is a range right now it's going to turn it into a table so if i go ahead and click on this
uh where's the data for your table well i'm selected right here that's not it it's this
right here i'm going to go ahead and select the making sure i have the right information selected
my table has headers it does because the name is up there so i'm going to go ahead and hit ok it's
going to open up power the power query editor now and you can see it's right in here so i haven't
done anything to this and what i want to do is split and you could see right here very quickly
if i go ahead and just select this and i'm going to pick by deliminator delimiters the uh like the
space that's in between or if it was a comma and it can kind of usually what it does is finds it
kind of it knows what it's searching for it found there's spaces in between this but i can quickly
change it if it was a comma or something else but in this case i'm going to use it just as this
and each occurrence of the delimiter i'll leave everything this is pretty simple i'm going to go
ahead and hit ok and it quickly split these i can go ahead and rename this so if i wanted this to be
uh you know if this was first i can go ahead and click in these uh and just rename and i'll put
last and if you wanted to change the order you could click and drag and change them around too
so in this case i will leave it as last name and then first like this when you're all done
you just go ahead and i'm going to hit close and load and it's going to create a brand new
tab at the bottom of your worksheet so i'm going to hit close and load and you can see
now it opens this up i have another table five here i can go ahead and adjust
uh the the length of these here if i needed to stretch any of these out to see all the different
uh names in here so those are four different ways i've shown you today that you can split
cells in microsoft excel so from using the flash fill to text to column to uh using a formula
to using this power query and all can be very effective it depends on really what you're looking
for so i hope you like this video here today i'll be putting out more excel videos all the time so
make sure you let me know what you're looking for thanks for watching this
week on teachers tech i'll see you next time with more tech tips and tutorials