Split one column in Excel into two 1

[vistor]

I have a column that contains both the last name and first name
such as:
Miller, Bob
Abercrombie, Nathan

I want to split this column into Last_Name and First_Name.
I have been trying to use the MID function but I can't get it.
I first want to extract every thing left of the comma to get the
Last_Name and then every thing right of the comma to get the
First_Name. Of course the column as it is now contains names of
different lengths.

Anyone know how to do this?

 

[JVFriederick]

Sure. If the first cell is located in C2

LastName
=MID($C2,1,FIND(",",$C2)-1)

FirstName
=MID($C2,FIND(",",$C2)+2,100)

 

[JVFriederick]

Another method is to use Data Text-to-Columns.
Both ways will work !

 

[vistor]

I don't know what you mean by Data Text-to-Columns.

 

[vistor]

Your first suggest worked:
LastName
=MID($C2,1,FIND(",",$C2)-1)

Can you please explain it to me? C2, start with the first character, FIND("everything up to the comma",in cell C2) -1 (does -1 mean extract what you find?)

 

[JVFriederick]

No problem.

MID function - 3 parts
1st part = what you wish to parse
2nd part = starting at what character number. For the LastName, we know this, = 1.
3rd part = how many characters long. This we don't know, but we can look for the "," within the cell. The FIND function returns the character number, or position where it found the string. But since it includes the "," we must subtract 1 from the result.

LastName for Miller, Bob cell would actually be
MID("Miller, Bob",1,7-1)

The FirstName equation is sort of backwards from the LastName. We must add 2 to where the "," character was found to account for the "," and the space. I used 100 for how many characters long to ensure we got everything.

FirstName for Miller, Bob cell would actually be :
MID("Miller, Bob",9,100)

That's all there is to it !

The second method
On the main Excel menu, at the top of the screen, File Edit View ..etc .... Data ..
Then select Text-to-Columns

Don't worry - when I try to be brief in the explainations, it runs a greater risk of being misunderstood. I'm sure you know what I meant now !! ;-)

 

[Guest_imported]

Thank you so much!!!!!!