How to Separate First and Last Names in Excel (6 Methods)

You have a column of full names like “John Smith,” and you need the first and last names in separate columns before you can sort, filter, or mail-merge anything. The right method depends on one question: do you want a one-time split, or a formula that updates when the names change?

Split first and last names in Excel

Here’s how the methods compare:

Method	Best for	Result	Works in older Excel
Flash Fill	Quick one-time split	Static values	Yes (Excel 2013 and later)
Text to Columns	Clean data with one delimiter	Static values	Yes
TEXTSPLIT	Formula that spills into both columns	Live formula	No
TEXTBEFORE / TEXTAFTER	Names with middle names	Live formula	No
LEFT / RIGHT / SEARCH	Excel 2021 and earlier	Live formula	Yes
Power Query	Recurring imports, large lists	Refreshable table	Yes

Fix #1: Let Flash Fill do it

Flash Fill copies a pattern you type once. It’s the fastest option if you just need clean values and don’t care about formulas.

1. Put your full names in column A, starting at A2.
2. Type First name in B1 and Last name in C1.
3. In B2, type the first name exactly as it should appear. For “John Smith,” type John.
4. Click B3 and press Ctrl + E.

Excel fills the rest of column B with first names. You can also click Data > Flash Fill if you prefer the ribbon.

5. Repeat in column C. Type Smith in C2, click C3, and press Ctrl + E.

Scroll through the results before you trust them. Flash Fill guesses from a pattern and doesn’t understand names, so middle names, suffixes like “Jr.,” and prefixes like “Dr.” can trip it up.

If nothing happens when you start typing, check that the feature is on. Go to File > Options > Advanced, and under Editing options, make sure Automatically Flash Fill is checked.

Fix #2: Split the column with Text to Columns

Text to Columns splits every cell at a character you pick, such as a space or comma. It’s predictable, and it handles cells with more than two parts, like an address or a full record crammed into one cell.

1. Select the column that holds the full names.
2. Click Data > Text to Columns.

3. Choose Delimited, then click Next.

4. Check Space as the delimiter. For names stored as “Smith, John,” check Comma instead.
5. Check Treat consecutive delimiters as one so extra spaces don’t create blank columns. Click Next.

6. Leave Column data format on General. Pick Date only if a column holds dates.
7. In the Destination box, enter an empty cell such as $B$2.

8. Click Finish.

Set the destination carefully. Text to Columns writes straight into the neighboring cells and overwrites anything already there. If you’re nervous, save a copy of the workbook to OneDrive first.

Fix #3: Use TEXTSPLIT for a live formula

TEXTSPLIT does the same job as Text to Columns, except it’s a formula. Change the name in column A, and the split updates on its own. It’s available in Microsoft 365 and Excel 2024.

1. Click B2.
2. Type this formula and press Enter:

=TEXTSPLIT(TRIM(A2)," ",,TRUE)

“John Smith” spills into two cells: John in B2 and Smith in C2. TRIM strips stray spaces, and the TRUE argument tells Excel to ignore back-to-back spaces.

3. Drag the fill handle in B2 down to copy the formula to the rest of your list.

The catch is that TEXTSPLIT breaks at every space. “Mary Jane Watson” becomes three cells, so use Fix #4 if your list has middle names.

Fix #4: Use TEXTBEFORE and TEXTAFTER for names with middle names

These two functions grab the text before or after a delimiter. They’re the cleanest way to get exactly two columns, even when some names have three or more parts.

1. In B2, enter this for the first name:

=TEXTBEFORE(TRIM(A2)," ")

2. In C2, enter this for the last name:

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

The -1 tells Excel to search from the end of the name. “Mary Jane Watson” returns Mary and Watson. “Juan de la Cruz” returns Juan and Cruz, which is wrong for that name (more on this below).

3. Copy both formulas down the column.

If some cells hold a single word with no space, those formulas return #N/A. The last argument, if_not_found, tells Excel what to return instead. These versions put a single-word name in the first-name column and leave the last-name cell blank:

=TEXTBEFORE(TRIM(A2)," ",1,0,0,TRIM(A2))

=TEXTAFTER(TRIM(A2)," ",-1,0,0,"")

For names stored as “Smith, John,” flip the logic and split on the comma:

• Last name: =TEXTBEFORE(A2,",")
• First name: =TRIM(TEXTAFTER(A2,","))

Fix #5: Use LEFT, RIGHT, and SEARCH in older Excel

TEXTSPLIT, TEXTBEFORE, and TEXTAFTER are only in Microsoft 365 and Excel 2024. Perpetual-license versions such as Excel 2021 and 2019 don’t have them. These classic formulas work in every version.

1. In B2, enter this to pull the first name:

=LEFT(TRIM(A2),SEARCH(" ",TRIM(A2))-1)

SEARCH finds the position of the first space. LEFT takes everything before it, and the -1 drops the space itself.

For “Smith, John,” swap the space for a comma: =LEFT(A2,SEARCH(",",A2)-1). That returns the last name.

2. In C2, enter this to pull everything after the first space:

=RIGHT(TRIM(A2),LEN(TRIM(A2))-SEARCH(" ",TRIM(A2)))

LEN counts the total characters. Subtracting the space’s position gives RIGHT the number of characters to grab from the end.

For “John Smith,” you’re done. For “John Q Smith,” column C shows Q Smith, since the formula only cuts at the first space.

3. To strip the middle initial, run the same RIGHT formula again on the result in column C. In D2, enter:

=RIGHT(C2,LEN(C2)-SEARCH(" ",C2))

If you’d rather use one formula that always grabs the last word, this one works in any version. It’s harder to read, but it saves the extra column:

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

Fix #6: Split names with Power Query

Power Query is worth the setup if you import the same kind of name list every week, or the list runs to thousands of rows. You build the split once, then click Refresh when new data arrives.

1. Select your data range.
2. Click Data > From Table/Range. Confirm the range and click OK.
3. In the Power Query Editor, click the name column’s header.
4. Click Transform > Split Column > By Delimiter. The same command also sits on the Home tab in some builds.
5. Pick Space (or Comma) as the delimiter.
6. Choose Left-most delimiter for “first name / everything else,” Right-most delimiter for “everything else / last name,” or Each occurrence of the delimiter to split every word.
7. Click OK, then double-click each new column header and rename it.
8. Click Home > Close & Load.

Excel drops the split names into a new table on a new sheet.

Names that won’t split cleanly

Splitting text on a space and finding someone’s actual surname are different problems. No Excel formula can figure out that “Juan de la Cruz” has the family name “de la Cruz” or that “李 小龙” puts the family name first. Pick a rule for your list before you split it:

• First word as the first name, last word as the last name (Fix #4)
• First word as the first name, everything else as the last name (Fix #5, step 2)
• Separate columns for prefixes like “Dr.” and suffixes like “Jr.”
• A short list of exceptions you fix by hand

Hyphenated names like “Anne-Marie Smith” stay together, because a hyphen isn’t a space.

Common errors when splitting names

“#NAME?” with TEXTSPLIT, TEXTBEFORE, or TEXTAFTER. Your version of Excel doesn’t have those functions. Check it at File > Account > About Excel. Switch to Fix #5, or get the newer functions through a Microsoft 365 subscription or an Excel 2024 license.

“#SPILL!” A TEXTSPLIT formula needs empty cells to spill into. Clear whatever sits in the columns to the right and the error goes away.

Blank columns appear between names. Extra spaces are creating empty splits. Wrap the cell in TRIM and add the TRUE argument, as in Fix #3. If the names came from a web page, they can contain non-breaking spaces that TRIM ignores. This version catches them:

=TEXTSPLIT(TRIM(SUBSTITUTE(A2,CHAR(160)," "))," ",,TRUE)

Formulas throw a syntax error. Some regional versions of Excel separate arguments with semicolons instead of commas. Try =TEXTSPLIT(TRIM(A2);" ";;TRUE). Function names can also be translated in non-English versions.

When nothing splits the way you want

If your list mixes “First Last” and “Last, First” in the same column, clean up the format first. Sort by whether the cell contains a comma, split each group separately, then combine them. For truly messy lists, Power Query’s Replace Values and conditional columns handle the cleanup without touching your original data.

Conclusion

For a one-off list, Flash Fill (Fix #1) solves this in about ten seconds and needs nothing beyond Ctrl + E. If the names change or you’re building a template, use TEXTBEFORE and TEXTAFTER (Fix #4), because they give you exactly two columns even when middle names show up. Either way, scan the results for multi-word surnames and titles, since that’s where every method quietly gets things wrong.