How to Split Full Names Into First and Last in Excel

The mail-merge problem

You have a column of full names. The mail merge, the CRM upload, the email tool, all of them want First Name and Last Name in separate columns. Excel has Text to Columns, but it splits on every space, so Ana Maria Lopez becomes three columns instead of First "Ana Maria" / Last "Lopez". Here's how to do it right.

TL;DR — Key takeaways

• Text to Columns with space delimiter works for two-word names but breaks on middle names.
• Flash Fill (Ctrl+E) learns the pattern from a few examples but is unpredictable on edge cases.
• LEFT/MID/RIGHT/FIND formulas can be precise but require knowing exactly which format you have.
• A free VBA macro handles First Last, First Middle Last, and Last, First formats with two one-shot picks.

What "full name" might actually mean

Real-world name columns include:

• John Smith (two-word)
• Ana Maria Lopez (first + middle + last)
• Robert Lee Johnson Jr (first + middle + last + suffix)
• Sharma, Priya (last comma first)
• O'Brien (single word, irreducible)
• de la Cruz, Maria (compound surname, comma form)

No automatic tool handles all of these correctly. The best you can do is pick a format that handles 90% of your data and accept that the remaining 10% need manual fixup.

Method 1: Text to Columns (works for two-word names)

Excel's classic split tool.

1. Select the name column.
2. Data > Text to Columns.
3. Choose Delimited > Next.
4. Check Space, click Next.
5. Pick the destination cell.
6. Click Finish.

Names like John Smith cleanly split into John and Smith. Names like Ana Maria Lopez split into three columns: Ana, Maria, Lopez. You then have to manually combine Ana Maria into one column.

The catch: middle names. Suffix names. Anything with three or more words.

Method 2: Flash Fill (Ctrl+E)

Excel's pattern-learning feature, available in Excel 2013 and later.

1. In the cell next to your first name, type the first name (e.g., John).
2. In the cell below, type the next first name. Excel may auto-suggest the rest of the column.
3. If it doesn't auto-suggest, press Ctrl + E to trigger Flash Fill.
4. Repeat for last name in the next column.

Flash Fill is fast when it works. It works on consistent formats (all First Last). It struggles when:

• Some entries are First Last and others are Last, First.
• Middle names appear inconsistently.
• The first few examples don't capture the variation in the data.

You also can't easily inspect the Flash Fill rule, so when it gets something wrong, you're guessing why.

Method 3: LEFT/MID/RIGHT/FIND formulas

For precise control:

For First Last:

First: =LEFT(A2, FIND(" ", A2) - 1)
Last:  =MID(A2, FIND(" ", A2) + 1, 100)

For Last, First:

Last:  =LEFT(A2, FIND(",", A2) - 1)
First: =TRIM(MID(A2, FIND(",", A2) + 1, 100))

For First Middle Last:

First (with middle): =LEFT(A2, FIND("@", SUBSTITUTE(A2, " ", "@", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))) - 1)
Last:                =MID(A2, FIND("@", SUBSTITUTE(A2, " ", "@", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))) + 1, 100)

The middle-name formula is using SUBSTITUTE to find the last space. It works but it's the kind of formula nobody can debug without writing it out on paper.

Method 4: The free VBA macro (multi-format, sane defaults)

Download Split Full Names Into First and Last. Free .xlsm with one macro.

Steps:

1. Select the single column of full names.
2. Press Alt + F8, pick SplitFullNames, click Run.
3. Type 1 for First Last format or 2 for Last, First format.
4. The macro fills First Name in the column to the right and Last Name in the column after that.

For First Last mode, anything before the last word becomes the First Name (so middle names go to First). For Last, First mode, the macro splits on the first comma.

A common scenario: weekly substitute teacher placement

You import a CSV of confirmed substitute teachers each Monday. Most names are First Last, a few are Last, First. About 80 names per week.

With the macro:

1. Select the name column.
2. Run the macro with format 1 (First Last).
3. The 65 simple names split correctly.
4. Filter the result by First Name = blank to find the comma-format entries.
5. Run the macro again on just those rows with format 2 (Last, First).
6. Done.

Total elapsed: about 90 seconds versus 6 minutes of Text to Columns plus manual fixup.

Handling middle names

The macro's behavior in First Last mode:

Input	First Name	Last Name
John Smith	John	Smith
Ana Maria Lopez	Ana Maria	Lopez
Robert Lee Johnson	Robert Lee	Johnson

This is what most CRMs want when they have only two name fields: everything before the last word in First, the last word in Last.

Handling Jr / Sr / III suffixes

The macro doesn't have special handling for suffixes. So Robert Lee Johnson Jr gives First "Robert Lee Johnson" and Last "Jr". About 1% of typical name lists include a suffix, so the failure rate is real but small. After running, sort by Last Name and you'll see all the Jr, Sr, III entries clustered; fix them by hand.

Frequently asked questions

Does it handle hyphenated last names like "Smith-Jones"?

Yes. Hyphens don't separate words in the macro's split logic; only spaces do. So Maria Smith-Jones correctly gives First "Maria" and Last "Smith-Jones".

What about apostrophe names like O'Brien?

Apostrophes are treated as part of the word. John O'Brien gives First "John" and Last "O'Brien" correctly.

What if the cell has only one word?

In First Last mode, single-word entries become First "Madonna" and Last "". In Last, First mode, single-word entries become Last "Madonna" and First "". Either way, no data is lost; you can spot single-word entries afterward by filtering for blank in either output column.

Will it overwrite the columns to the right of my source?

Yes. The macro writes First Name to the column directly to the right of your selection and Last Name to the column after that. If those columns have data, that data gets overwritten. Insert two blank columns first if you have something to keep.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. The macro only writes to the two adjacent columns, so undo cleanly removes the output without touching your source names.

What to do next

After splitting, if your name capitalization is inconsistent (PRIYA SHARMA next to john smith), run Convert Text to Proper Case to standardize. If the source column has whitespace contamination, run Trim Whitespace From All Cells before splitting.