Split a Full Name into First and Last

All versions

Imported a column of full names? These two formulas pull out the first and last name with no Text-to-Columns wizard and no add-ins — they update live as data changes.

Quick formula: First name up to the space, last name after it:

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

For the last name use =MID(A2,FIND(" ",A2)+1,LEN(A2)).

Functions used (tap for the full reference guide):

LEFT MID FIND LEN

The example

A column of full names that need to be split into First and Last.

	A	B	C
1	Full name	First	Last
2	Maria Lopez	Maria	Lopez
3	James Carter	James	Carter

The formula

FIND locates the space; LEFT grabs everything before it and MID everything after:

=LEFT(A2,FIND(" ",A2)-1) // text before the first space = first name

How it works

How the pieces fit:

FIND(" ",A2) returns the position of the first space — 6 in "Maria Lopez".
LEFT(A2, position-1) takes the 5 characters before the space: "Maria".
MID(A2, position+1, LEN(A2)) takes everything after the space: "Lopez". LEN is a safe upper bound for length.
Fill both formulas down; they recalculate automatically as names change, unlike Text-to-Columns.

On Excel 365 you can do the whole job with =TEXTBEFORE(A2," ") and =TEXTAFTER(A2," ").

Try it: interactive demo

Interactive

Type a full name; see the first and last name extracted.

Full name

Variations

Last name when a middle name exists

Grab everything after the last space using SUBSTITUTE to find it.

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

Modern 365 version

TEXTBEFORE and TEXTAFTER read like plain English.

=TEXTBEFORE(A2," ")

Pitfalls & errors

Names with no space (single word) make FIND return an error. Wrap in IFERROR if your data is messy.

Run TRIM first to strip stray leading or double spaces that throw off FIND.

Practice workbook

📊

Download the free Split a Full Name into First and Last practice workbook

Type any full name in the yellow column; First and Last fill in.

Frequently asked questions

What if names have middle names?

The basic last-name formula would include the middle name. Use the SUBSTITUTE/REPT variation to grab only the text after the final space.

Is Text-to-Columns easier?

It is a one-time split. Formulas are better when the source data keeps changing, because they update automatically.

Do I need Excel 365?

No — LEFT, MID, FIND, and LEN work in every version. TEXTBEFORE/TEXTAFTER are the simpler 365-only alternative.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: LEFT MID FIND LEN