Excel Split Names Tutorial

Data Translates
219 followers
In this Excel tutorial, we're going to look at how to split names in Excel.
And this video is one in a series that I've made about Excel.
So if you haven't watched my other Excel tutorials, please do so as soon as humanly possible.
But in this particular video, what we're going to be doing is looking at how to split names.
And this is a common problem in Excel.
Maybe you're working in a worksheet like this one, which has people's last names,
followed by a comma, and then their first names.
And this is very, very common when working with data.
And it's kind of nice to have it this way.
You can sort and alphabetize this information because it's listed as last name, comma, first name.
It's pretty nice.
But from time to time, it becomes important to separate these out and put the first name in column B, for example.
And how do you do that?
If you don't want to go into each cell one at a time and delete, and then copy-paste,
and put in the first name over here in column B, how do I separate these out?
Another common problem is having data like this, first name, space, last name.
And in some ways, this is even worse because it makes alphabetizing by last name very difficult.
Okay, so let's look at how to solve these problems.
I'm going to go back in to my first example, last name, comma, first name.
And like I said, this is not terrible.
But what if I wanted to find out how many people in this list have the first name of Jonathan,
or have the first name of Mary, or whatever?
It would be hard to do that with the first name and last name in the same column.
So the way to solve this problem is to click on the column letter.
That would be column A in this case.
And then all I have to do is make sure I'm on the data tab, the data ribbon,
and I go to the data tools group and look for text to columns.
I just click on that, and I get a pop-up that appears.
It's the convert text to columns wizard, and there's three steps.
First step is I have to choose which of these two descriptions best describes my data.
Is it delimited, characters such as commas or tabs separate each field?
So let's think about this.
Is it true that I have commas or tabs separating each field?
Yeah, I do.
I have commas between each one of these.
So I'm going to stick with delimited.
And then I'll click next.
And then it says this screen lets you set the delimiters your data contains.
And by default, it's got tab selected.
I'm going to uncheck tab.
And then I'll just go down and choose comma.
And as soon as I did that, notice what happened.
It showed me down here a data preview, and it showed me that it's going to work.
Now before I click next, notice that it says treat consecutive delimiters as one.
So for example, space.
There is also a space between the last name and the first name.
And I will have this checked, treat consecutive delimiters as one.
And so what that should do is it should get rid of this extra space between them.
So now I'll click next.
And now if I want to, I can set the column data format.
So I want to leave it as just general text.
But if I wanted to, I could specify it specifically as text or as a date.
And I could choose the format for that date that I want to use.
So I'll just leave it at general.
And I'll click finish.
And look what it did.
It successfully split the names.
The last names are here.
The first names are here.
It got rid of the commas.
And it got rid of the extra space with one exception.
It looks like Estelle had two spaces between her last name, the comma, and the first name.
And so I'm just going to delete that.
And now I've got perfectly split names.
Let's look now at the other problem, first and last names.
And the only thing separating them is a space.
It's almost the same.
I just click here on the column letter.
And then I go to data, text to columns.
Again, it's really delimited again.
So I'll click next.
This time, instead of comma, I'll just check space.
I can uncheck treat consecutive delimiters as one in this case.
And then I'll just click next.
I don't really want to change any of this.
I'll click finish.
And it splits those names perfectly.
Now that I've done this, now that I've split the names,
if I wanted the last name to be in the first column, it's easy to do.
All I need to do is go here to column A, right click in the column letter, and cut.
And then go over to column C, right click, and paste.
And you can see what that did.
It just copy pasted the first name from column A to column C.
I could even just right click now on A and delete that entire column.
And now I've got last names in column A, first names in column B.
So text to columns really is pretty simple, pretty easy.
But it's a nice Excel feature that a lot of people don't know exists.
So thanks for watching.
I hope you enjoyed this video.
If you did, please click the like button below.
And please consider connecting with me on my social media platforms like Facebook, Twitter, and Pinterest.
And please do subscribe to my YouTube channel for more videos about technology for teachers and students.
And watch for another video from me at least every Monday.
