Working with names in Excel can become confusing—especially when some entries include middle names and others don’t. If you’ve ever struggled to separate names into different columns, this guide will help you split first, middle, and last name in Excel easily.
In this article, we will cover both Flash Fill (CTRL + E) and formula-based methods so you can handle any type of data efficiently.
📊 Example Data
| Full Name |
|---|
| Pankaj Kumar |
| Rahul Dev Sharma |
| Amit Singh |
| Neha Rani Gupta |
| Rohit Verma |
🎯 Desired Output
| Full Name | First Name | Middle Name | Last Name |
| Pankaj Kumar | Pankaj | (blank) | Kumar |
| Rahul Dev Sharma | Rahul | Dev | Sharma |
| Amit Singh | Amit | (blank) | Singh |
| Neha Rani Gupta | Neha | Rani | Gupta |
| Rohit Verma | Rohit | (blank) | Verma |
⚡ Method 1: Flash Fill (CTRL + E)
In Microsoft Excel, Flash Fill allows you to quickly split names based on patterns.
Steps:
- Enter the First Name manually for the first two rows
- Press CTRL + E
- Repeat for Middle and Last names
⚠️ Important Tip:
If your data has mixed formats (some with middle names, some without), always provide both types of examples. Otherwise, Excel may place last names into the middle name column.
🔥 Method 2: Formula Method (Best for Automation)
If you want a reliable and dynamic solution, formulas are the best way to split first middle last name in Excel.
👉 Extract First Name
=LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1)👉 Extract Last Name
=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("@",SUBSTITUTE(TRIM(A2)," ","@",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")))))👉 Extract Middle Name
=IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))=1,"",
TRIM(MID(A2,FIND(" ",A2)+1,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))))💡 Method 3: TEXTSPLIT Function (Excel 365)
If you’re using the latest Excel version:
=TEXTSPLIT(A2," ")This function automatically splits names into multiple columns including first, middle, and last names.
