Intermediate
10 min read
Updated 21/09/2026
How to Split First and Last Names in Excel
Learn multiple effective methods to separate combined full names into distinct first and last name columns in Excel, using both built-in functions and advanced formulas.
In this tutorial:
- Using Text to Columns feature
- Splitting names with Flash Fill
- Formula-based separation methods
- Handling special cases and middle names
You'll need:
- Microsoft Excel (2010 or newer)
- Full names in a single column
- Basic Excel knowledge
How to Split First and Last Names in Excel
Method 1: Using Text to Columns
Copy the source column and choose an empty destination first: splitting on spaces creates one column per name part and may overwrite neighboring data. Review compound given and family names manually.
1
Select Your Data
Select the column containing the full names you want to split.
2
Open Text to Columns
Go to Data tab > Data Tools > Text to Columns.
3
Configure Settings
- • Choose "Delimited" and click Next
- • Select "Space" as delimiter
- • Click Next, then Finish
Method 2: Using Flash Fill (Excel 2013+)
1
Create Example
Type the first name from your first full name entry in the adjacent column.
2
Use Flash Fill
Press Ctrl + E or Data tab > Flash Fill. Excel will automatically detect the pattern.
Method 3: Using Formulas
For First Name:
The formulas below assume exactly two words separated by one space. They return an error for a single word and do not identify compound surnames. Clean spacing with TRIM first and review exceptions.
=LEFT(A2,FIND(" ",A2)-1)
For Last Name:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
Pro Tips and Best Practices
- Always create a backup of your data before splitting names
- For names with middle initials, consider using a more complex formula with MID function
- Test your chosen method on a small sample first to ensure it works with your specific data format
Common Issues and Solutions
Common Problems:
- Multiple spaces between names can cause incorrect splits
- Names with prefixes (Mr., Dr., etc.) may split incorrectly
Solutions:
- Use TRIM() function to remove extra spaces
- For names with prefixes, use Text to Columns with multiple delimiters