SPLIT NAMES in Excel | Separate First, Middle and Last Name

Hello and welcome to this Excel tips video. I'm Sumit Bansal and in this video I'm going to show you how to split names in Excel. That is how to separate the first, middle and last name in Excel using various methods. So let's get started. So here I have these names. I have the first name and the last name and I have this separate data set where I have the first name, middle name and the last name. Now I'm going to show you how to use text to columns to split this data. Now text to columns takes a specific character that you specify and then it splits the text string based on that character. So in this case I'm going to use the space character because my names are separated by a space character and then text to column is going to give me everything before the space character and after the space character. So I would select this entire data set. I would go to the data tab here and click on text to columns option. It opens this dialog box where it's a three step wizard. So in step one, I'm going to keep a delimited selected, which is selected by default. I would click on next. Here it asks me what's the delimiter and in this case I'm going to select space as the delimiter in case there is anything else selected, you deselect it and you select space or whatever delimiter you're using. In my case it is the space character. Now I would click on next here and here I can specify the destination cell. Now if I do not specify the destination cell, it is going to overwrite this data. So it is going to give me the first name here and the second name here or the last name here. But if I specify the destination cell here, then it'll keep the original data set as is and it is going to give me the first name here and the second name, the last name here. Also you can specify the format in which you want it because I have the text data, I would keep it general. But if you're working with date type data, then you can also specify the format in this case. Now it also shows you this data preview so you can quickly see that it has these names here. So in one column you would get all these names and in the second column you are going to get all these names. In case you do not want one specific kind of data, let's say you do not want the last name, you only want first name, then you select it and you click on do not import the column. So it is going to skip this column. In my case I'm going to keep it general because I want to have all these columns. Now I'm going to specify the destination cell, which would be B2 in this case. And now when I click on finish, it asks me if there is already data, then do you want to replace it? In this case there is no data, but sometimes it is going to give you this notification. You can simply click okay and it is going to give you the result. So I have the first name in this column and last name in this column. Similarly, I have this data set with the middle name and I can do the same thing using text to columns. So I would go to the data tab, click on text to columns. Here delimited is selected. I go to next, space is selected. I go to next here and I change the destination cell and click finish and click okay here. And it is going to give me the first name, middle name and last name in three separate columns. So this is really quick and you can use text to columns. The only thing you need to remember is that this result is static, which means that in case your original data set changes, this is not going to change. So you would have to do this again if you have a new data set. Here I have this name data set where I have the first name and the last name. And I also have this mixed data set where there are some names with middle names as well. Now what I want to do is I want to get the first name here and the last name here from this data set. So the first thing I'm going to do is copy this data and paste it here. Now I'm doing this because if I use find and replace on a data set, then that data set is changed and I do not want to lose my original data set here. So I've copied it here. Now I would go to the home tab and here in editing group I have find and select. And when I click on it, it shows me these options. I would click on replace. So this opens this find and replace dialog box. You can also use the keyboard shortcut control H and it'll open the find and replace dialog box. Now what I want to do is I want to keep the first name, but I want to remove everything which is after the space character. So including the space character, I want to remove everything after it. So in this case, the good thing with find and replace is that you can use wildcard characters. And a wildcard character can actually represent text string. So instead of using the exact text string, you can use a wildcard character. So let me show you what I mean here. What I'm going to find is a space character followed by an asterisk sign. Now, when I use this asterisk sign here, it represents any number of characters, which means that it is going to find anything in this data set that starts with a space character and can be followed by any number of characters, which would actually mean that it is going to find starting from the space character and the last name. And I'm going to replace it with nothing. So it is going to find all these last names, beginning with the space character and replace it with nothing. See what happens when I click on replace all. It instantly makes these replacements and I'm just left with the first name because the space character and the last name are gone because I replaced it with nothing. Similarly, if you just want the last name, you copy paste this here. And again, I would press control H. Now in this case, I'm going to change this because I want the last name, which means I want everything till the space character. So I would start with an asterisk sign and then put the space character. And now I'm going to replace it with nothing. So now I replace all. It instantly removes all the first name and the space character and I'm only left with the last name. You can also use find and replace to make the data consistent. For example, here I have this data set, which has some names with middle name, but some which do not have the middle name and I want to make it consistent. So I want to remove middle name from those names that have it. So I first copy this data here, press control H. And in this case, what I want is I want to remove everything which is after the first name and till the last space character in case there is a middle name. So I would come here in find what field and I will put space character asterisk space character. So start with the space character. There could be any number of characters because I do not know how long the middle name is. It could be one alphabet, it could be 10 alphabets and ends with a space character. And I'm going to replace it with one single space character because I still want the first name and the last name to be separated by a space character. So if I remove everything here, I still want this to be separated by a space character. And now when I click on replace all, it makes five replacement because there are some names which do not have a middle name. So if I find this, it cannot find it in this name Rick Nowak because there is no middle name. But in all the other names where there was a middle name, it found it and replaced it with a space character. So now I can click okay and this is done. So find and replace is a really, really quick and very fast method if you want to quickly remove or find and replace a part of a text tree. If you're using Excel 2013 or later versions, then you already have a tool in your Excel called Flash Fill. Now Flash Fill is really good when you're working with text data because it can identify patterns and it helps you get the data entry done a lot faster. So in this case, I have these names data set where I have the first name and the last name and I want to split these names. So I want to get the first name here in this column and the last name in this column. So I'm going to use Flash Fill to do this. See what happens when I type the first name here, Rick. I have to type this manually and then I type the second name. As soon as I'm typing it, Flash Fill is going to show me all these suggestions in gray and I can see that these are correct. It has picked up the first name from all these names. And if I think this is right, then all I have to do is hit the enter key and as soon as I do it, it is going to fill all these cells with the first name. Similarly, if I come here and I type the last name for the first cell manually and then I'm typing it for the second cell, it is going to show me all these suggestions and I can see these are correct. So I simply come here and I hit the enter key. So this is using Flash Fill that you can instantly manipulate the text data, extract part of the text data and put it in a cell. Now, in case you do not see the gray suggestions as I was seeing here in my Excel, let's say I remove this and I'm typing this here and you do not see it, there is nothing to worry about. You just hit the enter key, select these two cells and drag this down or you can double click and it'll fill it down. And at the bottom, you see this icon and you click on this Auto Fill Options icon and there is an option of Flash Fill. You can see the data is incorrect as of now, but as soon as I click on Flash Fill, it is going to fill it for me. So in case you do not see it, in most cases you would, but in case you don't see the Flash Fill suggestions, you can also do this. With Flash Fill, you can also rearrange the data. So for example, in this case, I have the first name and the last name, but I want to rearrange this data in a way that I have the last name, then comma, and then the first name. So you can do this quickly with Flash Fill. I would first type the first one and I need to give Flash Fill a little bit of suggestion or a pattern so that it can identify what I'm trying to do. This is why I always have to type in the first cell. So I type this here and now Flash Fill can identify. The pattern is last name followed by a comma, followed by a space character, and then followed by the first name. So now when I hit enter and I type the second one while I'm doing it, it shows me all these suggestions and I can see these are correct. So I hit enter. Similarly, you can also rearrange the data when you have a part of the text string that you do not want. So Flash Fill is going to identify that you want to remove a part of it and it is going to do that for you. For example, in this case, if I write Novik comma Rick, where I have removed the middle name because I don't want it, and I come here and I type, it can identify this is what I want. I want the last name followed by the first name, but I do not want the middle name here. So when I hit enter, it gives me this result. So you can see Flash Fill is really smart and in many, many cases it is going to be accurate. But in those rare cases where it is not able to identify the pattern, it is always good to first see what the result is and in case Flash Fill is not giving you the right result, then do it some other way. Also, you can remove part of the text string. So in this case, I have a middle name and I want to remove it. I can simply type the first name and the last name. And now when I come to the second cell and I type it, you can see it gives me these suggestions and instantly it is going to remove the middle name. The most important thing when you're using Flash Fill is to remember that it looks for a pattern. So if you have a consistent data set, then you can easily use Flash Fill to manipulate text data. Excel has a lot of text formulas that you can use to manipulate the text data. And in this example, I'm going to show you how to use formulas to separate the first, middle and last name. Now here I have this data set where I only have the first name and the last name, and I've already used a formula to separate these names, but let me show you how these formulas work here separately. Now in this name, if I only want to get the first name, what I really want is find the position of the space character because that is what is separating the first and the last name and extract everything which is to the left of it. So the first thing I need to do is search for the space character position in this name. And when I hit enter, it gives me five, which means five is the position of the space character. So I need to get the four characters, which is before the five, which is the position of the space character. So in this case, I need to extract these many characters, four characters from the left. So I would use the left function, and this is the text and the number of characters that needs to be extracted is given by this formula here, which is four in this case. So it gives me this name here. And if I drag this down, you can see it is going to give me the right names. Now, if you want to get the last name, you again have to find the position of the space character and then extract every right of it. So again, I start with searching for the space character in this name, which is five. And then I also need to know how many characters are there after the space character. So to do that, I would first look for the length of this entire string and how many characters are after the space character would be given by the difference of these two numbers. So if I come here and I say 10 minus five, then these are the number of characters that I need to extract. If I put it for all these cells, if I copy this part for all these cells, you can see in the second one, there are six characters that need to be extracted. In the third one, there are seven. So now all I need to do is use the right function. In this text, I want these many characters. And now when I hit enter, it gives me the right result. And now you can replace these references and make this formula combined. So the full formula that you're going to get is going to be this one, where this is the number of characters that I need to extract. And this is the right formula. Now, let's say you also have a middle name. And when you have a middle name, things are a little bit more complicated. So the first name is relatively easier. Actually, it is the same formula here, because in the first name case, you just have to identify the position of the first space character and extract everything to the left of it. So nothing changes. But when it comes to middle name and last name, things change a little bit. Let's go to the last name first and let me show you how to get this part here. Now, because there are two space characters, what I want to do is I want to identify the position of the second space character. So I'm going to use the substitute function where the text in which I want to substitute is this here in A14. And the text that I want to change is space character and I want to change it with something else. Let's say I want to change it with an at the rate character. Now I'm using at the rate character because I want something which is unique, which will not be repeated. I cannot use an alphabet because alphabets are repeated in the name, but I can use a hash symbol or a pipe symbol or an at the rate symbol because I know that would not be a part of the name. I want something which will be unique. And what's the instance number where I want to make this change? I do not want to make this change in the first instance. That's the first space character. I want to replace the second space character. So I would put two here and this is what I get. I get the same name here, but instead of the space character, I have at the rate sign. Now I'm going to find the position of this at the rate sign in this name and it gives me eight. Now it's just the same as this one. I know that after the eighth position, I have to extract everything. So I'm going to use the LEN function to get the total number of characters and I'm going to subtract eight from 30. So now this gives me the number of characters that I need to extract. So this is the total number of characters in the name. Eighth is the position of the at the rate, which I have added to a formula. And then it tells me that you need to get five characters from the right and extract these to get the last name. So I can simply now use the right formula. This is the text and I want to extract these many number of characters. And this is going to work if I copy it for all these names. You can see it gives me the right result. And when you combine this formula, so when you change these individual formulas and combine this with the cell reference here, so instead of A14 put the formula here and instead of I14 put the formula in all the cells, you get a combined formula, which looks like this. It's again, the same thing. I've done the same thing here, but here I just deconstructed it for you. Now, when it comes to the middle name, again, the same thing needs to be done. First, I'm going to look for the space character position in this name, which is five. Then I'm going to substitute the second space character position with an at the rate and find its position. So here I'm going to use substitute. I'm going to change the space character in this text. Old text would be space, new text would be at the rate and the instance number would be two. This gives me this name. Now I find out the position of at the rate in this name. So I use the search function. I'm looking for at the rate within this text here and it gives me eight. Now, it tells me that the space character starts from the fifth position and ends at the eighth position. So what I want is I want these characters in between these two positions. So I'm going to use the mid function here. This is my text and the start number here would be six because fifth is the space character position. So I want to start with this plus one and how many characters do I want? I want eight minus five minus one. So I want two characters here. So this is the eighth position. This is the fifth position. I want two in between. So I would start with, I want this minus this character position minus one. And now when I hit enter, it gives me this formula here, this result here. Now, if I copy this for all these cells, it is going to give me this formula. Now you can combine this formula. For example, in this case, it's using all these separate elements. I can simply bring these elements together. Let me show you in just this one case so that you know how to do this. I'm going to replace F14 with the real formula, which is this here and this here. Then the next thing is H14. So I'm going to come here and I'm going to use G14 formula, which is this one here. And I'm going to copy this here in G14. So now I have this combined formula in H14 and I'm going to change the reference of H14 to that formula. And you can see I have the right result. Let me quickly check if everything is correct. Yes, everything is correct. Everything only points to this name. Rest all is a formula so I can copy this formula, put it here and hit enter and gives me the right result. So this is how you can use text formulas, which includes formulas such as left, right, mid, substitute, search to extract part of a name. It could be first name, last name, or the middle name. Now, a good thing with formulas is that even if your original data set changes, because your formula is linked to the original data set, your resulting data is also going to change. So if you want something which is dynamic, if you have a list coming in of names and you want to quickly do it for all these names and you want to keep the result dynamic, then it is always better to use formulas. But if you are not comfortable using formulas and obviously these could get a little bit complicated, then you can use other techniques such as text to columns, find and replace, or flash fill. That's it in this video. I hope you found this useful. Also, if you want to be notified of any new Excel tips video that I come up with, please subscribe to this YouTube channel. Thank you and have a nice day.