Extract First & Last Name

Excel Formulas › Text

Excel 365Legacy altTEXTBEFORE/AFTER

Splitting Maria Lopez into a first and last name is a daily chore for mailing lists and imports. In Excel 365 TEXTBEFORE and TEXTAFTER make it trivial; in older versions LEFT/RIGHT with FIND get it done.

Quick formula: for a full name in A2:
=TEXTBEFORE(A2, " ") // first name =TEXTAFTER(A2, " ") // last name
TEXTBEFORE returns everything before the first space; TEXTAFTER everything after it.

Functions used (tap for the full reference guide):

TEXTBEFORETEXTAFTERLEFTRIGHTFIND

The example

Full names split into first and last.

ABC
1Full nameFirstLast
2Maria LopezMariaLopez
3Devon SmithDevonSmith
4Priya PatelPriyaPatel

The formula

First name in B2, last name in C2:

=TEXTBEFORE(A2, " ") → Maria =TEXTAFTER(A2, " ") → Lopez

How it works

Each function works from the space that separates the two names:

  1. TEXTBEFORE(A2, " ") returns everything to the left of the first space — the first name.
  2. TEXTAFTER(A2, " ") returns everything to the right of the first space — the last name.
  3. For names with a middle name, TEXTAFTER(A2, " ", -1) takes the text after the last space to isolate the surname.

Even faster for a whole column: Flash Fill. Type the first name once next to the data and press Ctrl+E; Excel fills the rest by pattern. Great for one-offs, but it doesn’t update if the source changes — formulas do.

Try it: interactive demo

Live demo

Type a full name; see the first and last name each formula returns.

First:   Last:

Variations

Legacy first name (LEFT + FIND)

Any version — characters up to the first space:

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

Legacy last name (RIGHT + LEN + FIND)

Everything after the first space:

=RIGHT(A2, LEN(A2) - FIND(" ", A2))

Handle a middle name

Take the surname as the text after the final space:

=TEXTAFTER(A2, " ", -1)

Pitfalls & errors

#N/A or #VALUE! on single-word entries. A cell with no space has nothing to split. Guard it: =IFERROR(TEXTAFTER(A2," "), "").

Extra spaces break the split. Double spaces or leading spaces from pasted data shift the result. Wrap the name in TRIM(A2) first.

TEXTBEFORE/TEXTAFTER need Excel 365. Older versions show #NAME? — use the LEFT/RIGHT/FIND formulas.

Practice workbook

📊
Download the free Extract First & Last Name practice workbook
Names split with TEXTBEFORE/AFTER, the LEFT/RIGHT/FIND legacy versions, and middle-name handling, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I separate first and last names in Excel?
In Excel 365 use =TEXTBEFORE(A2," ") for the first name and =TEXTAFTER(A2," ") for the last. In older versions use =LEFT(A2,FIND(" ",A2)-1) and =RIGHT(A2,LEN(A2)-FIND(" ",A2)).
How do I handle middle names?
Take the surname as the text after the final space with =TEXTAFTER(A2," ",-1). The -1 instance number means the last occurrence of the space.
Should I use Flash Fill instead?
Flash Fill (Ctrl+E) is great for a quick one-time split, but it doesn't recalculate when the source changes. Use formulas when the data updates.

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

Related formulas: Split text into columns · Clean extra spaces · Join text with a delimiter

Function references: TEXTBEFORE · TEXTAFTER · LEFT · RIGHT · FIND