Excel Split Cell Into First and Last Name: A Clear Guide

How-to basics · Updated November 21, 2026

To split a full name in Excel, select the name cells and use Data > Text to Columns > Delimited, then choose Space. For a repeatable split in Microsoft 365, use formulas: with a full name in A2, =IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2)) returns the first name, and =IFERROR(TEXTAFTER(TRIM(A2)," ",-1),"") returns the last word as the surname.

Choose a method that fits your names

The best option depends on whether this is a one-time cleanup and how names are written. Text to Columns is a quick built-in tool, formulas recalculate when the source changes, and Flash Fill is useful when you can show Excel examples of the pattern you want. All three can divide a name at a space, but a space does not always mark the boundary between a person’s given name and family name.

For a simple list such as “Maya Chen,” splitting on the space gives Maya and Chen. For “Maya Rose Chen,” however, there are three parts. You need to decide whether the middle name belongs in a separate column, should stay with the first name, or should be excluded from the output. Make that decision before splitting a large list, because Excel cannot infer everyone’s preferred name format from punctuation alone.

  • One-time split: Use Text to Columns, especially when each name has exactly two parts.
  • Changing or refreshed data: Use formulas so the results update when a source name changes.
  • Irregular but predictable patterns: Try Flash Fill, then review its results.

Split names with Text to Columns

Text to Columns separates text in one column into multiple columns using a delimiter, such as a space, comma, or tab. In Microsoft Excel for Microsoft 365 on Windows, the steps are:

  1. Make a copy of the worksheet or confirm that the columns to the right of the names are empty. The results can replace existing contents.
  2. Select the cells containing the names, without selecting unrelated rows or columns.
  3. Choose Data > Text to Columns.
  4. In the wizard, choose Delimited, then select Next.
  5. Check Space as the delimiter. If repeated spaces should count as a single separator, select Treat consecutive delimiters as one.
  6. Review the preview. Set a destination cell if you want to keep the original names; for example, choose B2 to place the split results in columns B and onward.
  7. Select Finish.

With “Maya Chen” in A2 and a destination of B2, the result is Maya in B2 and Chen in C2. A name with a middle name can create three or more output columns. If you leave the destination at the source, Excel may overwrite the original column as part of the split, so check the destination and surrounding data first. On Excel for Mac, the Text to Columns command is also on the Data tab; the wizard may look slightly different, but the delimiter and destination choices serve the same purpose.

Use formulas for a repeatable split

Formulas are useful when you want to preserve the source column or when the list may be updated. These examples assume each full name is in A2. Put the first formula in B2 and the second in C2, then fill them down using the fill handle or copy and paste. In Microsoft 365, TEXTBEFORE and TEXTAFTER are available; these functions also require Excel 2024 if you are not using Microsoft 365.

With the full name in A2, this formula returns the first word. For “Maya Rose Chen,” the result is Maya.

=IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2))

With the full name in A2, this formula returns the last word, searching from the end. For “Maya Rose Chen,” the result is Chen; for “Maya Chen,” it is also Chen.

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

TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. IFERROR handles a name with no space: the first-name formula returns the trimmed original, while the last-name formula returns a blank. These formulas treat the first and final words as the first and last names; they do not determine a person’s legal or preferred name structure. After checking the output, you can copy the formula results and use Paste Special > Values if you need fixed text rather than formulas.

Handle middle names and compound surnames

“First and last name” can mean different things in a spreadsheet. The formulas above take the first word and the final word, so they intentionally omit anything between them. That may be suitable for a display list, but it can lose information in a customer, employee, or mailing record. For example, “Maya Rose Chen” becomes Maya and Chen, while “Ana María de la Cruz” becomes Ana and Cruz. The latter person’s family name may include words that the simple split places in the middle.

If you need to retain middle names, choose a layout before applying the split. Text to Columns can put each space-separated part in its own column, after which you can combine the appropriate columns. Alternatively, use a formula that returns everything after the first space as a “remaining names” field rather than calling that field the last name. This preserves “Rose Chen” together, although it still does not identify surname boundaries in every naming convention.

For dependable records, keep the original full-name field and treat split fields as editable data, not unquestionable facts. When the names come from a form or database you control, collecting given name and family name separately is generally more reliable than trying to reconstruct them later from a single text field.

Use Flash Fill for a pattern-based split

Flash Fill detects a pattern from examples you enter and fills neighboring cells with similar results. It can be convenient when names have a consistent format but you do not want to write formulas. Suppose the full names are in A2:A20. Type the desired first name from A2 into B2, then start entering the next first name in B3. If Excel recognizes the pattern, it displays suggested results; press Enter to accept. You can also select the destination range and choose Data > Flash Fill. On Windows, Ctrl+E is the Flash Fill shortcut.

To extract surnames instead, enter the desired surname beside the first example and repeat the process in that output column. Flash Fill produces values rather than formulas, so its results do not update automatically if the source names change. Review the complete output, especially names with middle initials, hyphens, extra spaces, or compound surnames. If Excel guesses incorrectly, provide more examples or use a method with rules you can inspect.

Check spacing and preserve your source data

Extra spaces are a common reason a split produces unexpected blank columns or results. Text to Columns has an option to treat consecutive delimiters as one. The formulas shown use TRIM to clean ordinary spaces at the start and end and collapse repeated ordinary spaces between words. However, copied data can contain nonbreaking spaces or other characters that look like ordinary spaces but are not handled in the same way. If TRIM does not fix the result, inspect the source or clean the imported data before splitting.

Before changing a large worksheet, keep an untouched copy of the full-name column. Confirm that the selected range includes the intended records, that output columns are empty, and that the first and last rows split correctly. Sorting or filtering the original and output columns together helps prevent names from becoming separated from their associated records. If you need to share or import the result into another system, check whether that system expects a particular order, such as given name followed by family name.

Excel for the web and version differences

The instructions above focus on Excel for Microsoft 365 on Windows. TEXTBEFORE and TEXTAFTER are supported in Microsoft 365 and Excel 2024, but not in every older Excel edition. If a formula displays a function-name error, check your Excel version or use Text to Columns instead. Excel for Mac has the same general formula approach and provides Text to Columns from the Data tab, though the interface and keyboard shortcuts can differ.

Excel for the web can be suitable for formula-based cleanup when the functions are available in your account. The ribbon and availability of tools such as Flash Fill can vary, so use the visible Data tab commands or open the workbook in the desktop app if a needed command is absent. Regardless of platform, the important choice is how to interpret names with more than two parts—not just which button performs the split.

Frequently Asked Questions

Can Excel split a full name into first and last name automatically?
Yes. Use Data > Text to Columns with Space as the delimiter for a quick split, or use formulas to extract the first and final words. Check the results because names with middle names or compound surnames may not follow a simple two-part pattern.

How do I split a name into two columns without losing the original?
With Text to Columns, set the destination to an empty column, such as B2, before finishing. With formulas, leave the full name in column A and place the results in separate columns. Keeping the source also makes it easier to correct a mistaken split.

How do I get only the last name when there is a middle name?
In Microsoft 365 or Excel 2024, if the full name is in A2, =IFERROR(TEXTAFTER(TRIM(A2)," ",-1),"") returns the final word. It will not preserve a multiword surname as a unit, so review names that include particles or compound family names.

Why does Text to Columns put a name into more than two columns?
Space is the delimiter, so each separate word can become a new field. A middle name or suffix can therefore create a third or fourth column. Use the preview, keep the original data, and decide which words belong in each final field before deleting anything.

Can I split first and last names in Excel with a formula?
Yes. In Microsoft 365 or Excel 2024, TEXTBEFORE can return the first word and TEXTAFTER with an instance number of -1 can return the final word. These formulas recalculate when the source changes, unlike Text to Columns or Flash Fill results.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.