Split a full name into first and last name

Sample data. Full names in A, the formulas in B2 and C2 are copied down.

The first name

Click the first cell under First (B2) and type:

=LEFT(A2,FIND(" ",A2)-1)

B2 shows Ana. Copy it down and every row gets its own first name.

• FIND(" ",A2) gives the position of the first space, 4 in "Ana Lopez".
• -1 stops just before the space.
• LEFT takes that many letters from the start: 3, so Ana.

The last name

In C2, take everything after the space:

=MID(A2,FIND(" ",A2)+1,100)

C2 shows Lopez. The 100 means "up to 100 characters", more than any name needs. The same result with RIGHT: =RIGHT(A2,LEN(A2)-FIND(" ",A2)) also gives Lopez.

Names with a middle name

For "Raj K Patel" the first name formula still gives Raj, but the MID formula gives "K Patel", because it takes everything after the first space. To get only the last word:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

The result is Patel. SUBSTITUTE swaps each space for 100 spaces, RIGHT takes the last 100 characters (the last word plus padding), and TRIM removes the padding.

A "Last, First" list

When names look like "Lopez, Ana", split at the comma instead. First name:

=TRIM(MID(A2,FIND(",",A2)+1,100))

gives Ana (TRIM drops the space after the comma). Last name:

=LEFT(A2,FIND(",",A2)-1)

gives Lopez.

A one word name gives #VALUE!

A name like "Cher" has no space, so FIND fails and the first name formula shows #VALUE!. IFERROR shows the whole name instead:

=IFERROR(LEFT(A2,FIND(" ",A2)-1),A2)

Cher now shows Cher, and two word names work as before.

Extra spaces

A space before the name makes the first name come out empty, because the first space is at the very start. Wrap A2 in TRIM, which removes spaces at the ends and turns double spaces into single ones:

=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)

"  Kim   Nguyen " gives Kim, and =MID(TRIM(A2),FIND(" ",TRIM(A2))+1,100) gives Nguyen.

Without formulas

Leave empty columns to the right of the names first, because the split fills the next columns. In Excel: Data > Text to Columns, choose Delimited, then tick Space. In Google Sheets: Data > Split text to columns. The result is plain text, so it does not update when a name changes; the formulas do. LEFT, MID, FIND, TRIM, SUBSTITUTE, REPT and IFERROR work in every current version of Excel and in Google Sheets.

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.