How to Separate First and Last Names in Microsoft Excel

If you’ve ever imported a list of names into Excel and found them all crammed into one column, you know how frustrating it can be. Whether you’re managing a contact list, organizing employee data, or prepping a marketing spreadsheet, splitting first and last names into separate columns is a simple, powerful move. Here’s how to do it fast.

Method 1: Use “Text to Columns”

This is the quickest built-in option for cleanly formatted names like “John Smith.”

Steps:

1. Select the column with full names.
2. Go to the Data tab in the ribbon.
3. Click Text to Columns.
4. Choose Delimited, then click Next.
5. Check the box for Space (or whatever character separates the names), then click Next.
6. Choose where you want the split data to go (either overwrite the current column or push it to the right), then click Finish.

Done. Excel separates the names into two columns.

Pro Tip:

If some names have middle initials or multiple last names (like “Mary Ann Smith” or “John O. Doe Jr.”), this method might mess things up. For that, check out Method 3 below.

Method 2: Use Excel Formulas

Prefer formulas? This method gives you more control and works even if names aren’t perfectly formatted.

For First Name:

=LEFT(A2, FIND(" ", A2) - 1)

For Last Name:

=RIGHT(A2, LEN(A2) - FIND(" ", A2))

Replace A2 with the actual cell reference.

Note:

This works best when there’s only one space between first and last name. If there are middle names or extra spaces, use Method 3.

Method 3: Use Power Query for Complex Name Splits

When things get messy—middle names, double-barreled last names, inconsistent spacing—Power Query is your friend.

Steps:

1. Select your name column.
2. Go to Data > Get & Transform > From Table/Range.
3. In Power Query, right-click the column header and choose Split Column > By Delimiter.
4. Choose Space as the delimiter and select how many times to split (usually “At the first occurrence” or “Each occurrence”).
5. Click Close & Load to send the cleaned-up data back to Excel.

This method gives you full control over how names are split and where each piece ends up.

Wrapping Up

Don’t let jumbled name columns slow you down. Whether you’re a beginner using Text to Columns or a power user diving into formulas or Power Query, Excel has tools to make the job easy. Start with Method 1 for clean data, but keep the other options in your back pocket for when things get tricky.

Got a spreadsheet that’s messier than average? Drop a comment below and I’ll help you sort it out.