How to Separate Names in Excel

Data Translates
219 followers
Hi everyone, my name is Kevin.
Today I wanna show you how you can separate first names
from last names in Microsoft Excel.
And this won't require any formulas at all.
It's extremely easy to do.
In fact, anyone can do this.
And I'm gonna show you step-by-step
how you can accomplish this.
And as full disclosure, before we jump into this,
this is something my HR department requires me to say.
I work at Microsoft as a full-time employee.
All right, well what are we waiting for?
Let's start separating values.
Here I am in Microsoft Excel.
This is the latest and greatest version
that comes with Office 365.
And I have a list of full names here.
So I have a big list of names.
And I have two other columns here,
one for the first name and one for the last name.
And it's kind of lonely right now
because there are no values in here.
So it seems pretty simple.
You just have a first name, you just have a last name.
But as I go down the list, I'm going down, down, down,
at the very bottom there's a name
with the middle name in here.
So how do I separate out the first name from the last name
and ignore the middle name?
Now in the past, many people have used different formulas
to be able to extract the first name
and then to extract the last name.
But those tend to get complicated
and they're not very user-friendly.
So how can we get these values out?
Well, what we're gonna do is
we're gonna use the magic of tables.
What I'm gonna do is I'm gonna highlight
all of this data here.
And I'm gonna go up to the insert pivot.
And within insert, there's an option to insert a table.
So we're gonna go ahead and click on that.
Here it says, where is the data for your table?
I already highlighted that.
And I also have this option that says
my table has headers.
Well, it does because I have full name,
first name, last name, that's my header.
So I'll make sure that's checked.
And then I'll click on OK.
All right, well, I made it a table.
So how do I get the first name out
and how do I get the last name out?
What I'm gonna do is I'm just gonna type it.
I'm gonna type in the first name.
And then we're gonna type in the next first name.
And as I do that, you'll notice what happened is
underneath, as soon as I started typing the next name,
you see that the column just kind of filled up underneath
with all the first names pulled out.
So what's happening is Excel is smart enough to realize
that I'm just typing out the first name
and it notices that I'm, in each case,
I'm picking out the first name.
So I'm just gonna hit Enter.
And you'll see that it automatically filled
all those values all the way down
and it pulled out the first name.
Now what I could do is let me try this
with the last name too.
So I'm gonna type in Maynard.
I'm gonna go through, type that in,
make sure I type that properly, hit Enter.
I'm gonna type in Zimmerman.
And here again, as I start typing it,
Excel is smart enough to realize that
I'm typing in the last name here.
So that all looks good.
So let me go ahead and just hit Enter
and there it just fills in the values all the way down.
And one thing you'll see that's very nice
is as I scroll down, here it pulled out the last name Rice.
It ignored the middle name.
So what it detected is that I was typing in a last name
and not just the second word.
And so it was able to extract that for me.
So here, in fact, even if I typed in,
let's say Margaret Miller Benson Rice,
even in that case, it keeps Rice in there.
So pretty nice thing that Excel does.
It just automatically detects the data that I'm entering
and it automatically separates it for me.
I don't have to enter in any formulas.
I don't have to use text to columns.
It's just super easy and simple for me
to get the first name out and the last name out.
Another little tip that I could do,
so if I just remove all these values here,
what I can also do is if I go under Zimmerman,
I could press Control E,
and what that'll do is that'll simply fill down.
So that's another way I could get it to fill.
Once again, that's on your keyboard,
you press Control and then E.
E is an equalizer, so we're equal.
So I'm gonna type in E
and then that also fills all the way down.
And that's really how easy it is
to separate a first name from a last name
in Microsoft Excel.
All right, well, hopefully you found that pretty easy.
If you were able to successfully separate out
a first name from a last name in your spreadsheet,
please give this video a thumbs up.
If you wanna see more videos like this in the future,
please hit that Subscribe button.
That way you'll get a notification
anytime new content like this comes out.
And lastly, if there are any other videos
that you wanna see me cover in the future,
leave a comment down below.
I read them all and I'll add it
to my list of videos to create.
All right, well, that's all I have for you today.
I'll see you next time, bye.
