How to Separate a Person’s First and Last Name in Excel

Here are the formulas for separating first name and last name from a person’s full name. Let us take the following sample data of NBA players

Let us first start separating First Name from the Player’s name. Type the this formula in C6 Cell.

=LEFT(B6,FIND(” “,B6))

The above formula first finds the single space then Left trims character starting from first character until the start of the single space. Now Autofill this formula for the rest of C6 columns to display the first name.

To get the last name of LeBron James (Cell B6) you can use this formula

=RIGHT(B6,LEN(B6)-FIND(” “,B6) )

What the above formula does is to first get the character count of full name then find starting position of ” “. Then uses RIGHT function to get the characters starting from ” ” till the end of the name. Now drag the formula entered for D6 to rest of the D column.