How do I separate names in Excel?

To separate names in Excel, select the full-name column and go to Data → Text to Columns → Delimited, then tick Space so the first and last name land in separate columns. Formulas (LEFT/RIGHT/FIND) or Flash Fill do the same thing. Sigmera separates a full-name column in one click in your browser, with no upload, and handles middle names automatically.

How do I separate text into columns in Excel?

Use Text to Columns: select the column, open Data → Text to Columns, choose Delimited, and pick the character that separates the values (a space for names, a comma for CSV-style text). Excel writes each part into its own column. Set a Destination cell first so you do not overwrite the original data.

How do I split first and last name in Excel?

Select the name column, go to Data → Text to Columns, choose Delimited, tick Space as the delimiter, set a destination cell, and click Finish. Excel splits each name at the space into separate columns. For two-word names this gives a clean first and last name in two columns.

How do I split names without Text to Columns?

Use formulas. For the first name use =LEFT(A2, FIND(" ", A2) - 1). For the last name use =RIGHT(A2, LEN(A2) - FIND(" ", A2)). Or, in Excel 2013 and later, type the first split result manually and press Ctrl+E to trigger Flash Fill, which detects the pattern and fills the rest.

How do I handle middle names or two-word last names?

Text to Columns splits on every space, so 'Mary Jane Watson' becomes three columns. To keep everything after the first space as the last name, use =MID(A2, FIND(" ", A2) + 1, LEN(A2)) for the last name and =LEFT(A2, FIND(" ", A2) - 1) for the first name. Tools that detect name structure handle these cases automatically.

How do I split names in Google Sheets?

Select the column and choose Data → Split text to columns, then pick Space as the separator. Or use the SPLIT function: =SPLIT(A2, " ") spills the parts across columns. =INDEX(SPLIT(A2," "),1) returns just the first name.

Can I separate names in Excel for the web?

Not with Text to Columns. Microsoft lists the Convert Text to Columns Wizard for Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 — the desktop app — and the Flash Fill article carries the same desktop-only list. In Excel for the web you are left with formulas such as =LEFT(A2, FIND(" ", A2) - 1), or a browser tool. Sigmera runs in the browser tab itself, so it works wherever Excel for the web does, and the file stays on your device.

How do I separate names stored as "Last, First"?

Split on the comma, not the space, then swap the two columns. In Text to Columns choose Delimited and tick Comma; "Smith, Jane" becomes Smith in the first column and Jane in the second — the reverse of what a CRM import expects. Excel also leaves a leading space on the second part, so wrap it in TRIM. A name splitter with a name-order setting writes the columns the right way round in one pass.

Is there a newer formula than LEFT and RIGHT to separate names in Excel?

Yes. Use =TEXTBEFORE(A2, " ") for the first name and =TEXTAFTER(A2, " ", -1) for the last. The -1 tells Excel to search from the end of the cell, so a middle name no longer breaks the split. Microsoft lists TEXTBEFORE and TEXTAFTER for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac; on Excel 2021 or older they return #NAME? and you fall back to LEFT/RIGHT/FIND.

Can TEXTSPLIT separate first and last names in Excel?

Yes. =TEXTSPLIT(A2, " ") spills every space-separated part of the name into the cells to the right, so Jane Smith fills two columns and Anna Maria Smith fills three. Add TRUE as the fourth argument, =TEXTSPLIT(A2, " ", , TRUE), so a double space is ignored instead of producing an empty cell. Because the number of columns follows the number of words, TEXTSPLIT is best for inspecting a list; when every row must land in exactly two columns, use TEXTBEFORE and TEXTAFTER. Microsoft lists TEXTSPLIT for Excel for Microsoft 365 and Excel 2024 only.

How do I extract the middle name in Excel?

Take everything after the first space, then everything before the last space of what remains: =IFERROR(TEXTBEFORE(TEXTAFTER(A2, " "), " ", -1), ""). For Anna Maria Smith that returns Maria; for a two-word name like Jane Smith there is no inner space, Excel would return #N/A, and the IFERROR wrapper turns that into a blank cell. Run TRIM on the column first, because a stray double space counts as an extra delimiter.

How do I separate names in Excel that have a suffix like Jr. or III?

Counting from the end puts the suffix where the surname should be: =TEXTAFTER(A2, " ", -1) on "John Smith III" returns III. Move the count one place in — =TEXTAFTER(A2, " ", -2) — to pick up Smith, and use =TEXTBEFORE(A2, " ") for the first name. Because suffixes appear in only some rows, sort the column so the suffixed names group together and apply the second formula to that block, or split the whole column with a name splitter that reads the structure per row.

Why do my split names turn into #REF! when I delete the original column?

Because a formula split stays linked to the source cell. =LEFT(A2, FIND(" ", A2) - 1) reads column A, so deleting column A leaves #REF! behind. Convert first: select the new columns, Copy, then Paste → Values Only. Microsoft notes that this permanently removes the formulas, so keep a copy of the sheet until you have checked the result. Text to Columns writes plain text and is not affected.

How do I split names in Excel so the split repeats next month?

Use Power Query rather than Text to Columns. Load the sheet with Data → From Table/Range, select the name column, and choose Split Column → By Delimiter with Space as the delimiter and Split at set to Left-most delimiter. The split is saved as a query step, so next month's export runs through the same transformation with a refresh instead of a repeat of the wizard.

What is the difference between Text to Columns and Power Query for splitting names?

Text to Columns edits the cells in place, once, and writes plain text. Power Query builds a repeatable query: the source file stays untouched, the split re-runs on refresh, and you can rename the output columns before they land. Power Query also lets you cut at the left-most or right-most space instead of every space, which is what keeps a middle name from creating a third column. The trade-off is setup time — for a single file, Text to Columns or a one-click name splitter is faster.

Why did my split names land in the wrong column?

Almost always an extra space. Text to Columns cuts at every space, so "Jane Smith" with two spaces produces an empty column and pushes the surname one column to the right. Run =TRIM(A2) over the name column first and split the trimmed copy. Microsoft describes TRIM as removing "all spaces from text except for single spaces between words", which is exactly the shape a split needs.

Does TRIM fix every stray space in a name column?

No. Microsoft notes that TRIM "was designed to trim the 7-bit ASCII space character (value 32)" and does not remove the non-breaking space, Unicode 160, which is what arrives when names are pasted from a web page or a CRM report. Strip that one first with =TRIM(SUBSTITUTE(A2, CHAR(160), " ")), then split.

Why does Text to Columns overwrite my data?

By default Text to Columns writes the results starting in the original column, overwriting whatever is to the right. Always set the Destination in the final wizard step to an empty cell, or copy the name column to a blank area first so the original list is preserved.

How to separate names in Excel: split first and last name

To separate names in Excel, select the full-name column, open Data → Text to Columns, choose Delimited, and tick Space as the delimiter so the first and last name land in separate columns. For an error-proof option that handles middle names automatically, Sigmera splits a full-name column in one click, in your browser, with no upload.

Last updated: October 2026

Split names in Excel with Text to Columns

Text to Columns is the standard way to split a full-name column. It splits each cell at a chosen character — here, the space between words — and writes each part into its own column.

1. Select the name column. Highlight the column of full names, and leave at least two empty columns to the right for the results.
2. Open Text to Columns. On the Data tab, click Text to Columns, choose Delimited, and click Next.
3. Choose the space delimiter. Tick Space so each word goes to its own column. Check the live preview, then click Next.
4. Set the destination and finish. Pick the first empty destination cell so the original column is not overwritten, then click Finish.

Split names with formulas

If you want the split to update automatically when names change, use formulas instead. For the first name:

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

For the last name:

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

To keep everything after the first space as the last name (useful for two-word surnames), use =MID(A2, FIND(" ", A2) + 1, LEN(A2)).

Use Flash Fill (Excel 2013+)

In the column next to your names, type the first name from row 1, then start typing the second and press Ctrl + E. Flash Fill detects the pattern and fills the rest of the column. Repeat in a new column for last names. Flash Fill is fast but does not update when the source data changes.

It is also guessing, and it does not report which rows it was unsure about — which is why the middle initials and the compound surnames are worth checking by hand afterwards. How Flash Fill infers a pattern covers what makes the guess reliable, why the preview sometimes never appears, and the Google Sheets equivalent.

Separate names with TEXTBEFORE and TEXTAFTER (Excel 365 and 2024)

Newer builds of Excel replace the LEFT/RIGHT/FIND pair with two functions that read as plain English. For the first name:

=TEXTBEFORE(A2, " ")

For the last name, count from the end instead of the start. The third argument is the instance of the delimiter, and Microsoft documents that "a negative number starts searching text from the end" (Microsoft, TEXTAFTER function), so -1 means the last space in the cell:

=TEXTAFTER(A2, " ", -1)

That pairing is what makes these two worth the switch for names. =TEXTBEFORE(A2, " ", -1) returns everything before the last space, so "Mary Jane Watson" gives Mary Jane and Watson instead of three columns — the case that breaks Text to Columns. Add "" as the sixth if_not_found argument to return a blank rather than #N/A on a single-word row.

Check your version before you rely on this. Microsoft lists both functions for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac (Microsoft, TEXTBEFORE function). On Excel 2021 or older they return #NAME?, and the LEFT/RIGHT/FIND formulas above are the fallback.

Trim the spacing before you separate the names

The most common reason a split lands one column too far right is an extra space. Every method on this page cuts on the space character, so "Jane Smith" with a double space gives Text to Columns three parts: Jane, an empty string, and Smith. The surname moves a column over, and on a 10,000-row export only some rows shift — which is why the damage usually surfaces after the CRM import, not before it.

Fix the column before you split it. In an empty cell put =TRIM(A2) and fill it down. Microsoft describes the function as removing "all spaces from text except for single spaces between words" (Microsoft, TRIM function) — leading, trailing and doubled spaces all collapse, which is the exact shape a delimiter split expects. Paste the result as values, then run the wizard on that column.

One space survives TRIM. Microsoft is explicit that the function "was designed to trim the 7-bit ASCII space character (value 32) from text" and does not remove the non-breaking space, Unicode 160 — the character that arrives whenever names are pasted out of a web page or a CRM report. It looks identical in the cell and it is not a delimiter, so Text to Columns walks straight past it and the whole name stays in one column. Substitute it out first:

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

If that formula is the third one you have stacked on a column you have not split yet, the browser name splitter normalises the whitespace as part of the split, so both space characters are handled before the first and last name are written out.

How to separate text in Excel that is not a name

Text to Columns is not name-specific. The same wizard separates any column that has a consistent character between the parts — a comma in "Berlin, Germany", a hyphen in an order code, a slash in a date written as text. Pick the delimiter that actually appears in your data instead of Space, and the wizard does the rest.

One step people skip: Microsoft's own instructions tell you to "Select the Destination in your worksheet which is where you want the split data to appear" (Microsoft, Convert Text to Columns Wizard). Leave that box on the default and Excel writes over the columns to the right of your data. Point it at an empty column first.

Lock the split in before you delete the full-name column

A formula split is live, not finished. Your first-name column still points at the original cell, so the moment you delete the full-name column every result collapses to #REF!. Sorting the sheet or importing it into a CRM can break it the same way. Text to Columns does not have this problem — it writes plain text — but LEFT, RIGHT, TEXTBEFORE, TEXTAFTER and Flash Fill all do.

Convert the results to values first. Select the two new columns, Copy, then Paste and pick Values Only from Paste Options. Microsoft is blunt about what that costs: "When you replace formulas with their values, Excel permanently removes the formulas" (Microsoft, Replace a formula with its result), so keep a copy of the sheet until the split looks right. Only then delete the original column.

The name splitter skips the step entirely: it writes finished text columns, never formulas, so there is nothing to convert and nothing to break on import.

Split names with Power Query when the export repeats

Text to Columns is a one-off. If the same full-name column arrives every week from the same CRM export, Power Query records the split as a step you re-run on the next file instead of walking the wizard again.

In Excel, load the sheet with Data → From Table/Range, then in the editor select the name column and choose Split Column → By Delimiter. Microsoft documents three entry points for it: the "Home tab —under the Split column dropdown menu inside the Transform group", the Transform tab, and a right-click on the column itself (Microsoft, Split columns by delimiter). Pick Space, and set Split at to Left-most delimiter so the cut happens at the first space only — "Mary Jane Watson" becomes Mary and Jane Watson rather than three columns.

Two things to expect. The new columns inherit the source name with a number on the end: Microsoft notes that "a suffix that includes a dot and a number that represents the split sections of the original column is appended to the name of the new columns", so Full Name arrives as Full Name.1 and Full Name.2 and you rename them before loading back to the sheet. And leaving Split at on every occurrence reproduces the Text to Columns problem — Power Query "splits the column into as many columns as needed", so a single three-word row widens the whole table.

Power Query is worth the setup only when the file repeats. For one export, the wizard or the browser splitter is quicker. The reverse job — pushing first and last name back into a single column for a label or a mail merge — is covered in joining two columns into one in Excel.

Split names in Google Sheets

Select the column and choose Data → Split text to columns, then pick Space as the separator. Or use the formula =SPLIT(A2, " ") to spill the parts across adjacent columns automatically.

If the names are still sitting in a CSV

Text to Columns needs a worksheet, so a raw export has to become one first. Rather than double-clicking the .csv — which is where accented surnames turn into mojibake and long ID columns turn into scientific notation — run it through the CSV to XLSX converter and open the workbook it writes. The name column arrives as text, spelled the way the source spelled it, which is the state Text to Columns actually expects. When the split is done and the importer wants a CSV again, convert the formulas to values first, then turn the XLSX back into a CSV — the export keeps what each cell displays, so First and Last leave as two separate columns.

Faster way → do it in one click with the free Split Full Names tool, no upload. It separates first and last name, handles optional middle names, and previews the result before you download.

Do this in 1 click — free

Manual Excel vs Sigmera, compared

Method	Time	Handles edge cases	Skill needed
Sigmera (this tool)	Seconds, one click	Detects middle names; first/last in one pass; live preview	None
Text to Columns	A minute (wizard)	Splits on every space; middle names break it	Low (menus)
LEFT/RIGHT/FIND formulas	Several minutes	Manual handling for middle and two-word names	Medium (formulas)

Frequently asked questions

How do I separate names in Excel?: To separate names in Excel, select the full-name column and go to Data → Text to Columns → Delimited, then tick Space so the first and last name land in separate columns. Formulas (LEFT/RIGHT/FIND) or Flash Fill do the same thing. Sigmera separates a full-name column in one click in your browser, with no upload, and handles middle names automatically.
How do I separate text into columns in Excel?: Use Text to Columns: select the column, open Data → Text to Columns, choose Delimited, and pick the character that separates the values (a space for names, a comma for CSV-style text). Excel writes each part into its own column. Set a Destination cell first so you do not overwrite the original data.
How do I split first and last name in Excel?: Select the name column, go to Data → Text to Columns, choose Delimited, tick Space as the delimiter, set a destination cell, and click Finish. Excel splits each name at the space into separate columns. For two-word names this gives a clean first and last name in two columns.
How do I split names without Text to Columns?: Use formulas. For the first name use =LEFT(A2, FIND(" ", A2) - 1). For the last name use =RIGHT(A2, LEN(A2) - FIND(" ", A2)). Or, in Excel 2013 and later, type the first split result manually and press Ctrl+E to trigger Flash Fill, which detects the pattern and fills the rest.
How do I handle middle names or two-word last names?: Text to Columns splits on every space, so 'Mary Jane Watson' becomes three columns. To keep everything after the first space as the last name, use =MID(A2, FIND(" ", A2) + 1, LEN(A2)) for the last name and =LEFT(A2, FIND(" ", A2) - 1) for the first name. Tools that detect name structure handle these cases automatically.
How do I split names in Google Sheets?: Select the column and choose Data → Split text to columns, then pick Space as the separator. Or use the SPLIT function: =SPLIT(A2, " ") spills the parts across columns. =INDEX(SPLIT(A2," "),1) returns just the first name.
Can I separate names in Excel for the web?: Not with Text to Columns. Microsoft lists the Convert Text to Columns Wizard for Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 — the desktop app — and the Flash Fill article carries the same desktop-only list. In Excel for the web you are left with formulas such as =LEFT(A2, FIND(" ", A2) - 1), or a browser tool. Sigmera runs in the browser tab itself, so it works wherever Excel for the web does, and the file stays on your device.
How do I separate names stored as "Last, First"?: Split on the comma, not the space, then swap the two columns. In Text to Columns choose Delimited and tick Comma; "Smith, Jane" becomes Smith in the first column and Jane in the second — the reverse of what a CRM import expects. Excel also leaves a leading space on the second part, so wrap it in TRIM. A name splitter with a name-order setting writes the columns the right way round in one pass.
Is there a newer formula than LEFT and RIGHT to separate names in Excel?: Yes. Use =TEXTBEFORE(A2, " ") for the first name and =TEXTAFTER(A2, " ", -1) for the last. The -1 tells Excel to search from the end of the cell, so a middle name no longer breaks the split. Microsoft lists TEXTBEFORE and TEXTAFTER for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac; on Excel 2021 or older they return #NAME? and you fall back to LEFT/RIGHT/FIND.
Can TEXTSPLIT separate first and last names in Excel?: Yes. =TEXTSPLIT(A2, " ") spills every space-separated part of the name into the cells to the right, so Jane Smith fills two columns and Anna Maria Smith fills three. Add TRUE as the fourth argument, =TEXTSPLIT(A2, " ", , TRUE), so a double space is ignored instead of producing an empty cell. Because the number of columns follows the number of words, TEXTSPLIT is best for inspecting a list; when every row must land in exactly two columns, use TEXTBEFORE and TEXTAFTER. Microsoft lists TEXTSPLIT for Excel for Microsoft 365 and Excel 2024 only.
How do I extract the middle name in Excel?: Take everything after the first space, then everything before the last space of what remains: =IFERROR(TEXTBEFORE(TEXTAFTER(A2, " "), " ", -1), ""). For Anna Maria Smith that returns Maria; for a two-word name like Jane Smith there is no inner space, Excel would return #N/A, and the IFERROR wrapper turns that into a blank cell. Run TRIM on the column first, because a stray double space counts as an extra delimiter.
How do I separate names in Excel that have a suffix like Jr. or III?: Counting from the end puts the suffix where the surname should be: =TEXTAFTER(A2, " ", -1) on "John Smith III" returns III. Move the count one place in — =TEXTAFTER(A2, " ", -2) — to pick up Smith, and use =TEXTBEFORE(A2, " ") for the first name. Because suffixes appear in only some rows, sort the column so the suffixed names group together and apply the second formula to that block, or split the whole column with a name splitter that reads the structure per row.
Why do my split names turn into #REF! when I delete the original column?: Because a formula split stays linked to the source cell. =LEFT(A2, FIND(" ", A2) - 1) reads column A, so deleting column A leaves #REF! behind. Convert first: select the new columns, Copy, then Paste → Values Only. Microsoft notes that this permanently removes the formulas, so keep a copy of the sheet until you have checked the result. Text to Columns writes plain text and is not affected.
How do I split names in Excel so the split repeats next month?: Use Power Query rather than Text to Columns. Load the sheet with Data → From Table/Range, select the name column, and choose Split Column → By Delimiter with Space as the delimiter and Split at set to Left-most delimiter. The split is saved as a query step, so next month's export runs through the same transformation with a refresh instead of a repeat of the wizard.
What is the difference between Text to Columns and Power Query for splitting names?: Text to Columns edits the cells in place, once, and writes plain text. Power Query builds a repeatable query: the source file stays untouched, the split re-runs on refresh, and you can rename the output columns before they land. Power Query also lets you cut at the left-most or right-most space instead of every space, which is what keeps a middle name from creating a third column. The trade-off is setup time — for a single file, Text to Columns or a one-click name splitter is faster.
Why did my split names land in the wrong column?: Almost always an extra space. Text to Columns cuts at every space, so "Jane Smith" with two spaces produces an empty column and pushes the surname one column to the right. Run =TRIM(A2) over the name column first and split the trimmed copy. Microsoft describes TRIM as removing "all spaces from text except for single spaces between words", which is exactly the shape a split needs.
Does TRIM fix every stray space in a name column?: No. Microsoft notes that TRIM "was designed to trim the 7-bit ASCII space character (value 32)" and does not remove the non-breaking space, Unicode 160, which is what arrives when names are pasted from a web page or a CRM report. Strip that one first with =TRIM(SUBSTITUTE(A2, CHAR(160), " ")), then split.
Why does Text to Columns overwrite my data?: By default Text to Columns writes the results starting in the original column, overwriting whatever is to the right. Always set the Destination in the final wizard step to an empty cell, or copy the name column to a blank area first so the original list is preserved.