In Excel, Splitting a Last_Name, First_Name cell, into 2 cells

Assuming that cell B2 contains a name like, Dangerfield, Rodney, here are 2 formulas you can use to create 2 cells, one with the first name, one with the last name.

For the first name:
=RIGHT(B2,LEN(B2)-FIND(",",B2)-1)

and for the last name:
=LEFT(B2,FIND(",",B2)-1)

Next, copy those formulas down thru all apllicable rows.

Lastly, highlight the 2 columsn, right-click and choose Copy.
Then right-click on an empty column head to the right, and choose: Paste Values.

paste-as-values

Special Case

What if the name is really, “last_name, first_name middle_name”, and you don’t want the middle name?
=LEFT(RIGHT(B2,LEN(B2)-FIND(",",B2)-1),FIND(" ",RIGHT(B2,LEN(B2)-FIND(",",B2)-2)))

That’s great, but what if someone does NOT have a middle name?
Then you’ll get an error.
To solve for that, need to make it even fancier:
=IF(ISERROR(LEFT(RIGHT(B2,LEN(B2)-FIND(",",B2)-1),FIND(" ",RIGHT(B2,LEN(B2)-FIND(",",B2)-2)))),RIGHT(B2,LEN(B2)-FIND(",",B2)-1),LEFT(RIGHT(B2,LEN(B2)-FIND(",",B2)-1),FIND(" ",RIGHT(B2,LEN(B2)-FIND(",",B2)-2))))

Leave a Reply

Your email address will not be published. Required fields are marked *