How to Split First and Last Name in Excel

To split names that contain just a first name and a last name, select the cells and choose Data > Text to Columns > Delimited, select Space, and finish with an empty destination beside the original data. For more control, use formulas or Flash Fill. The right choice depends on whether names include middle names, compound surnames, or inconsistent spacing.

What You’ll Need

A worksheet with one full name in each cell, such as names in column A, starting at A2.
Blank columns to the right for the separated names. Text to Columns can overwrite existing cells, so make space before you begin.
For the formula method, Microsoft 365 or Excel 2024. The formulas below use TEXTBEFORE and TEXTAFTER, which are available in those versions.
A clear rule for what counts as the first and last name. A formula can identify the first and final words, but it cannot know whether a surname such as “van Buren” should stay together.

Step-by-Step

Check the source data and choose a method. Keep the original full-name column until you have reviewed the results. If every entry has exactly two words separated by a space, Text to Columns is the quickest one-time option. If you want the first and final words in dedicated columns while leaving the source untouched, use formulas. Flash Fill is useful when the names vary and you can provide clear examples. Names with middle names or multiword surnames need extra review whichever method you choose.
Split simple names with Text to Columns. Select the full-name cells, excluding the header if there is one. In Excel for Microsoft 365 on Windows, choose Data > Text to Columns. In the wizard, select Delimited, then choose Next. Select the Space delimiter. If some entries contain two or more spaces between words, select Treat consecutive delimiters as one so Excel does not create empty columns. The preview shows how the names will be divided.
Set a safe destination and finish the split. On the final wizard screen, check the preview and set Destination to the first blank cell where the results should go, such as $B$2. This leaves the source in column A and places the first split word in column B. Select Finish, then check several rows. For a two-word entry such as “Maya Chen,” the result should be “Maya” in column B and “Chen” in column C. If you leave the destination at the source location, Excel may replace the original names with the split columns.
Use formulas to extract the first and last words. With full names in A2:A20 and blank output columns B and C, enter the following formula in B2 to return the first word. For the example “Maya Chen” in A2, the result is Maya. The formula also trims ordinary extra spaces, and for a one-word name it returns that word.

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

With the same layout—full names in A2:A20, first names in B, and last names in C—enter this formula in C2 to return the final word. For “Maya Chen” in A2, the result is Chen; for “Maya Rose Chen,” it returns Chen, not the middle name Rose.

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

Fill the formulas down and verify them. Select B2 and C2 and drag each fill handle down to the last row, or copy the formulas and paste them into the remaining cells in each column. Check a two-word name, a name with a middle name, and a single-word entry if your list contains one. In this setup, a one-word name appears in column B and column C stays blank. The formulas update if the original text in column A changes. When the results are correct and you need fixed text rather than formulas, copy the result columns and choose Home > Paste > Paste Values.
Try Flash Fill when examples can show the pattern. Leave the source names in column A. Type the desired first name from A2 into B2, then type the matching last name from A2 into C2. Start entering the next example in row 3; Excel may preview the rest of the pattern. If the preview matches your intended split, press Enter. If no preview appears, select the cells to fill and choose Data > Flash Fill, or use the Windows shortcut Ctrl+E. Use separate examples for the first and last name columns, and check the output because Flash Fill infers a pattern rather than applying a formal name rule.
Review and label the results. Add headings such as First name and Last name above the output columns. Scan the full list for blank results, unexpected extra columns, or names that need manual correction. Keep the original column until you have confirmed the split and saved the workbook. On Excel for Mac or Excel for the web, ribbon labels or available commands can vary by build; look for Text to Columns or Flash Fill on the Data tab. The formulas work in Microsoft 365 and Excel 2024 where these functions are available.

Common Mistakes

Splitting on spaces without considering middle names. Text to Columns separates at every space, so “Maya Rose Chen” becomes three columns, not a first-name column and a last-name column. The formulas above return the first and final words, leaving Rose out of those two outputs. Decide whether middle names should have their own column, be retained with the first name, or be handled manually.

Overwriting data to the right. Text to Columns writes its results into neighboring cells. If those cells contain information, that information may be replaced. Insert blank columns or choose a different destination before selecting Finish.

Assuming every final word is a complete surname. “Ana María de la Cruz” has a multiword surname, while the final-word formula returns only “Cruz.” The split is technically consistent but may not match your records’ naming convention. Do not use an automatic result as a definitive legal or identity record without checking it.

Forgetting to account for spacing and hidden characters. Repeated ordinary spaces can create blank columns in Text to Columns unless you select the consecutive-delimiters option. TRIM removes repeated regular spaces in the formula examples, but it does not remove every invisible character, such as a nonbreaking space copied from a website.

Tips

Make a copy of the worksheet or keep the original full-name column. This gives you a way to compare the results and recover the source if a split goes wrong. For a one-time cleanup of straightforward two-word names, Text to Columns is fast. For a list that will change over time, formulas are more convenient because the output recalculates when the source cell changes. Flash Fill creates extracted text rather than a live formula, so run it again if the source data changes.

Use a few representative examples before filling a large list. Include names with extra spaces, a middle name, a hyphen, an apostrophe, and any compound surnames in your data. Hyphens and apostrophes are not spaces, so the methods generally keep them within a word—for example, “Anne-Marie O’Neil” splits into “Anne-Marie” and “O’Neil.” A space in a surname, by contrast, creates a separate word and may need a manual decision.

If the names came from a web page or another system, check whether the spaces are ordinary spaces. If a formula does not split text that looks spaced, a nonbreaking space may be present. You can clean those characters in a helper column or replace them in the source before splitting. Avoid deleting the original values until you have reviewed the cleaned output and confirmed it still matches the source records.

For a reusable workflow, make the expected output explicit: first token, final token, middle name in a separate field, or a surname preserved as a phrase. No built-in split can reliably infer every cultural naming convention from the text alone. Where the output affects mailing, payroll, legal, or customer records, manually verify names that do not follow the common pattern.

Troubleshooting

The formula shows #NAME? or Excel does not recognize TEXTBEFORE. Your Excel version may not include TEXTBEFORE and TEXTAFTER. These functions are available in Microsoft 365 and Excel 2024. Use Text to Columns for simple entries, or update Excel if your organization provides a version with these functions.

The formula returns an error for a name with no space. The examples use IFERROR to handle this case: the first-name formula returns the trimmed single word, and the last-name formula returns a blank. If you still see an error, check for unusual characters or confirm that the source cell contains text.

Text to Columns creates extra blank columns. Go back to the wizard and select Treat consecutive delimiters as one on the Delimited screen. If the data contains tabs as well as spaces, inspect the preview and select only the delimiters that match the source. A delimiter that appears in the data can split a name unexpectedly.

Flash Fill produces incorrect results or no preview. Provide another example that demonstrates the intended pattern, then try Data > Flash Fill again. If the list contains several naming patterns, Flash Fill may apply one pattern inconsistently. Use formulas for predictable first-and-final-word extraction, or correct those rows manually.

Some names stay joined even though they look separated. The gap may be a nonbreaking space or another invisible character. Try replacing the unusual space with a regular space, then run the split again. When you cannot identify the character safely, copy a few affected values into a plain-text editor to inspect or re-enter the separator, and compare the cleaned values with the original.

Frequently Asked Questions

Can Excel split first and last names automatically?
Yes. Text to Columns splits on a selected delimiter, and Flash Fill can infer a pattern from examples. Formulas can return the first and final words. Review names with middle names or compound surnames because automatic methods cannot reliably determine the intended name structure.

How do I split a name into two columns when it includes a middle name?
The formulas above return the first word and final word, so a middle name is omitted from those two results. If you need to preserve all parts, use Text to Columns to place each word in its own column, then combine or assign the pieces according to your rules. Check every multiword surname manually.

Will these methods change the original name?
Text to Columns can replace source cells if its destination overlaps them. Set a blank destination first. The formulas and Flash Fill can place results in other columns, leaving the source unchanged. Keep that source column until you have checked and saved the results.

Can I split names with hyphens or apostrophes?
Yes, if the name uses spaces between first and last name. A hyphen or apostrophe is not treated as a space delimiter, so “Jean-Luc Picard” becomes Jean-Luc and Picard. Confirm that the source actually uses a regular space between the parts.

Why does TRIM not fix every extra space?
Excel’s TRIM removes repeated ordinary spaces but does not remove nonbreaking spaces, which are common in copied web data. Replace those characters with regular spaces or clean the source before using the formulas. Check the resulting names against the original to avoid accidental changes.

Which method should I choose?
Choose Text to Columns for a quick split of simple, consistently formatted names. Choose formulas when you want results that update with the source and have Microsoft 365 or Excel 2024. Choose Flash Fill when you can demonstrate a pattern and are prepared to review its guesses.

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.