Whether you’ve imported customer data, downloaded a report, or received a spreadsheet with full names in a single column, you’ll often need to split those names into separate first and last name columns. Fortunately, Excel offers a couple of quick ways to do exactly that.
In this tutorial, you’ll learn two easy methods for separating first and last names in Microsoft Excel using Flash Fill and Text to Columns. We’ll explain when each method works best so you can choose the right approach for your data.
What You’ll Learn
- How to separate first and last names using Flash Fill
- How to split names using Text to Columns
- When to use each method
- The differences between Flash Fill and Text to Columns
- How to avoid accidentally overwriting existing data
- Which method works best for imported data
Common Uses
- Cleaning imported customer lists
- Separating employee names
- Organizing mailing lists
- Preparing CRM or database imports
- Formatting contact information
- Preparing data for mail merge
- Splitting other delimited text (such as cities and states)
Requirements / Compatibility
This tutorial was originally recorded in 2020, and both methods remain fully supported in current versions of Microsoft Excel, including:
- Microsoft 365
- Excel 2024
- Excel 2021
- Excel 2019
- Excel 2016
- Excel for the web*
- Excel for Mac*
*Flash Fill availability varies slightly depending on the platform and version, while Text to Columns is widely available across desktop versions of Excel.
Practice & Resources
Want to work through the examples yourself? This tutorial includes a practice workbook you can use while following along with the video and step-by-step instructions.
The workbook includes:
- Instructions worksheet with a summary of both techniques
- Flash Fill practice worksheet
- Text to Columns practice worksheet
Click the button below to view the resources available for this tutorial.
Video Tutorial
Breaking Down the Concept
Excel provides two simple ways to separate names stored in a single cell. Both work best on simple “First Last” names; anything with un-hyphenated double last names may need a manual touch-up afterward, just as the tutorial notes.
Method 1: Flash Fill
Flash Fill looks for patterns in the data you enter and automatically fills the remaining rows.
For example:
| Full Name | First Name |
|---|---|
| John Smith | John |
| Sarah Jones | Sarah |
Once Excel recognizes the pattern, it completes the remaining names automatically.
Best for:
- Quick cleanup
- One-time data fixes
- Lists with consistent formatting
Method 2: Text to Columns
Text to Columns splits data based on a delimiter such as:
- Space
- Comma
- Tab
- Semicolon
In this tutorial, we use a space delimiter to separate first and last names.
Best for:
- Large imported datasets
- Consistent formatting
- Repeating data-cleaning tasks
Which Method Should You Use?
| Method | Best For |
|---|---|
| Flash Fill | Quick one-time cleanup and pattern recognition |
| Text to Columns | Large datasets and consistently formatted text |
Step-by-Step Instructions
Method 1: Flash Fill
- Put full names in one column (e.g., column A).
- In the next column, type the first name that matches row 2.
- Press Enter, click back into that cell, then go to Data > Flash Fill (or press Ctrl+E).
- Excel detects the pattern and fills the rest of the column automatically.
- Repeat in the next column for last names.
Method 2: Text to Columns
- Make sure you have a couple of empty columns to the right of your names (Text to Columns will overwrite whatever’s there).
- Select the column with full names.
- Go to Data > Text to Columns.
- Choose Delimited, click Next.
- Uncheck “Tab” and check only “Space” as the delimiter — you’ll see a live preview of the split.
- Click Finish.
Additional Tips
- Flash Fill works best when your data follows a consistent pattern. If names are formatted differently, review the results carefully.
- Text to Columns replaces the existing data to the right of the selected column, so make sure those columns are empty before you click Finish.
- If your names include middle names, suffixes (Jr., Sr.), or multiple last names, you may need additional cleanup after splitting.
- Press Ctrl + E to use the Flash Fill keyboard shortcut instead of navigating through the Ribbon.
Related Features
You may also find these Excel features helpful:
- TEXTJOIN
- CONCAT
- LEFT
- RIGHT
- MID
- TRIM
- SUBSTITUTE
- Flash Fill
Related
How to Remove Extra Spaces in Excel
Learn how to remove extra spaces from text in Microsoft Excel using the TRIM function. This beginner-friendly tutorial explains what TRIM removes, when to use it, and why it’s one of the most useful functions for cleaning imported or inconsistent data.
How to Create an Order Form with Drop-Down Menus in Microsoft Excel
Learn how to create a professional order form in Microsoft Excel using drop-down menus, Data Validation, and named ranges. This step-by-step tutorial also covers formatting dates, ZIP codes, and credit card fields to build cleaner, more accurate spreadsheets for real-world business use.
Stay Updated
There are two easy ways to get notified when new tutorials are published:
Subscribe on YouTube for new video tutorials: @SimpleSoftwareTutorials
or enter your email below to receive new blog posts directly in your inbox:
Simple Software Tutorials 