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.

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 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: September 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.

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.

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.
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 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.