How to Separate First and Last Name in Excel: Three Ways

Almost every list export I've ever seen puts the whole name in one column: webinar sign-ups, shop orders, event tickets, form entries. Then you want to send an email that starts "Hi Sarah" rather than "Hi Sarah Jones", and suddenly you need first names in a column of their own.

Excel gives you three ways to split them, and they suit different situations. Flash Fill is the fastest, Text to Columns is the most predictable, and the TEXTSPLIT formula keeps updating when new names arrive. I'll walk through all three, then deal with the awkward names that trip each one up.

Separating names in Excel with Flash Fill

Microsoft's help page uses splitting first and last names as its example for Flash Fill, which spots a pattern from what you type and fills in the rest.

1. Make sure there are two empty columns to the right of your full names, say B and C, with headings First name and Last name.
2. In B2, type the first name from A2 exactly as you want it, then press Enter.
3. Start typing the next first name in B3. Excel shows a greyed-out preview of the whole column.
4. Press Enter to accept it.
5. Repeat in column C with the last name.

If no preview appears, Microsoft says you can run it yourself from Data, then Flash Fill, or press Ctrl + E. If it never offers previews, the setting may be off: look under Options, Advanced, Editing Options and tick Automatically Flash Fill.

Flash Fill works in Excel for Windows and Excel for Mac, according to Microsoft. On a Mac, Data then Flash Fill, or Ctrl + E, does the same job.

One catch with Flash Fill

The results are plain typed values, not formulas. If you add new names to column A later, Flash Fill won't update them. Run it again for the new rows.

Splitting full names with Text to Columns in Excel

Text to Columns splits text wherever it finds a character you choose. For names, that's the space.

1. Select the column of full names.
2. Go to Data, then Text to Columns.
3. In the Convert Text to Columns Wizard, choose Delimited, then Next.
4. Under Delimiters, tick Space and untick anything else. The Data preview shows how the split will look.
5. Select Next.
6. Set the Destination to the first empty column where you want the results, for example B2.
7. Select Finish.

Watch the Destination box closely. If you leave it pointing at the original column, the first names replace your full names and the last names spill into the next column along, over anything already there. I always point it at a clear column, or work on a copy of the sheet.

If your list is in "Jones, Sarah" format, tick Comma and Space together, and the wizard splits on both.

Using TEXTSPLIT to separate names with a formula

Microsoft describes TEXTSPLIT as working the same as the Text to Columns wizard, but in formula form. The big difference is that it updates if the full name changes.

1. Click B2.
2. Type =TEXTSPLIT(A2," ") and press Enter.
3. The first name lands in B2 and the last name spills into C2 automatically.
4. Copy B2 down the column for the rest of the list.

Microsoft lists TEXTSPLIT for Excel for Microsoft 365 and Excel 2024, so it may not appear in older versions. If you type it and get a #NAME? error, your version doesn't have it, and Flash Fill or Text to Columns is the way to go.

Handling middle names and tricky names in Excel

Real lists are messy, and each method reacts differently:

Name in the list	Text to Columns or TEXTSPLIT	Flash Fill
Sarah Jones	Two clean columns	Two clean columns
Sarah Anne Jones	Three columns, middle name in the middle	Usually follows your example
Mary Ann Van Der Berg	Many columns, needs tidying	May guess wrong, check carefully
Sarah Jones (two spaces)	An empty column in between	Usually fine

For double spaces with TEXTSPLIT, Microsoft's help explains an optional ignore_empty setting. Set it to TRUE, as in =TEXTSPLIT(A2," ",,TRUE), and consecutive spaces are treated as one. Its default is FALSE, which creates an empty cell.

For compound surnames, I'd split the easy ones automatically, then sort by the third column to find any name that broke into more pieces than expected, and fix those few by hand.

Why splitting names matters for small business lists

For anyone running a side-hustle or small business, clean first names are what make a mailing feel personal. A few places I'd use this:

• Personalising newsletter greetings before importing a list.
• Sorting customers by surname for a printed guest list or delivery run.
• Matching names between two lists when one has full names and the other has first and last separately.

If you ever need to go the other way and rebuild full names from two columns, my guide on joining two columns into one in Excel covers the reverse trick. And once the names are split, resizing the new columns to fit makes the list far easier to read.

If separating names in Excel goes wrong

• Flash Fill fills nonsense: type two or three examples before accepting, so Excel can see the pattern clearly.
• Data was overwritten: press Ctrl + Z straight away, then rerun Text to Columns with a different Destination.
• Spill error with TEXTSPLIT: the cells to the right aren't empty. Clear them so the result has room.
• Stray spaces: names copied from forms often carry trailing spaces. Check a few cells before you split.

Frequently asked questions

What's the fastest way to separate first and last names in Excel?

Flash Fill. Type the first name from the first row in the next column, start typing the second, and press Enter when Excel previews the rest. Ctrl + E runs it manually.

How do I split names without overwriting my data?

In Text to Columns, set the Destination to an empty column before selecting Finish. Otherwise the results can replace the original names and the column beside them.

Will Flash Fill update when I add new names?

No. Flash Fill fills values, not formulas, so run it again for new rows. TEXTSPLIT is a formula, so it updates when the full name changes.

Why doesn't TEXTSPLIT work in my Excel?

Microsoft lists TEXTSPLIT for Excel for Microsoft 365 and Excel 2024. In older versions, use Flash Fill or Text to Columns instead.

How do I handle middle names when splitting?

Text to Columns and TEXTSPLIT split at every space, so a middle name gets its own column. Flash Fill follows the example you type, so show it exactly what you want.