Lanternhill Studio · Bank CSV Categorizer

How to Split First and Last Name in Excel

The examples assume full names like Maria Lopez are in column A starting at A2. Pick the method that fits your Excel version and how messy the names are.

1. Text to Columns (any version)

  1. Insert two empty columns to the right of the names so nothing is overwritten.
  2. Select the names (A2 down).
  3. Go to Data → Text to Columns, choose Delimited, click Next.
  4. Tick Space (and untick Tab), click Next.
  5. Set Destination to $B$2 and click Finish.

Names with a middle name split into three columns, so check the results. The output is static; it won't update if the names change.

2. TEXTSPLIT, TEXTBEFORE and TEXTAFTER (Excel for Microsoft 365, Excel 2024, Excel for the web)

Split on every space; the result spills across columns:

=TEXTSPLIT(A2," ")

For just first and last name, even when there's a middle name:

=TEXTBEFORE(A2," ")
=TEXTAFTER(A2," ",-1)

The -1 tells TEXTAFTER to use the last space, so "Maria J. Lopez" gives "Lopez". Wrap A2 in TRIM() if there are stray spaces.

3. LEFT, RIGHT and FIND (all versions)

First name (everything before the first space):

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

Everything after the first space (fine for two-word names):

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

Last word only, even with a middle name:

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

This replaces each space with 100 spaces, takes the last 100 characters, and trims them, which leaves the last word. If some cells hold a single word, FIND returns an error; wrap the formula as =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2).

"Last, First" format

For names like Lopez, Maria:

=LEFT(A2,FIND(",",A2)-1)
=TRIM(MID(A2,FIND(",",A2)+1,LEN(A2)))

The first gives the last name and the second gives the first name.

4. Flash Fill (Excel 2013 and later)

  1. In B2, type the first name exactly as you want it, e.g. Maria.
  2. Click B3 and press Ctrl+E (or Data → Flash Fill).
  3. Excel fills the column by copying your pattern. Do the same in C2 for last names.

Flash Fill guesses from your example, so scan the results, especially for middle names, suffixes like Jr., and particles like "van der". Its output is static.

No method handles every name. Titles (Dr.), suffixes (Jr., III) and multi-part surnames need checking by hand or a smarter parser.

Frequently asked questions

What is the quickest way to split names in Excel?

Data → Text to Columns with a space delimiter works in every version. Flash Fill (Excel 2013 and later) also works well for consistent names.

Which formulas split a full name?

In Excel for Microsoft 365, TEXTSPLIT, TEXTBEFORE and TEXTAFTER. In any version, LEFT, RIGHT and FIND around the space.

What about middle names?

Text to Columns and TEXTSPLIT create an extra column for each extra word; formulas based on the last space keep only the final word as the last name.

Skip the formulas: the free Split Names and Addresses tool does this in your browser. Nothing is uploaded.

Want more than a quick fix?

Just starting out? Grab the free Starter Expense Tracker (pay what you want, $0 is fine).

Guides