4 Ways to Split Cells in Microsoft Excel

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 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 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