How to Separate First and Last Name in Excel: 3 Easy Methods

How to Split Names in Excel - Quick Steps

• Open MS Excel>>Select the Column
• Open Text to Columns Wizard
• Choose Delimited>>Select Delimiters
• Set Destination>>Adjust Columns

Managing datasets with full names in a single column can be challenging when you need the names split into first and last names. Knowing how to separate first and last names in Excel is a practical skill that improves data clarity and organization. Whether you're working on a mailing list, a contact sheet, or any dataset requiring distinct name columns, Excel offers straightforward solutions.

Using tools like Text to Columns, Flash Fill, Excel formulas or formulas like LEFT, RIGHT, and SEARCH, you can effortlessly break names into individual components. This guide will walk you through step-by-step methods to split names in Excel, saving time and ensuring your data is well-structured and ready for analysis.

How to Separate Names in Excel: 3 Methods

We’ll cover three primary methods to split names in Excel:

Method 1: How to Split Names in Excel Using Text to Columns

The Text to Columns feature is one of the easiest ways to separate First and Last name in Excel. Here's how to use it:

Step 1: Open your Excel Spreadsheets

Open your Excel spreadsheet containing the listing of complete names you need to split.

Step 2: Select the Column Containing the Entire Names

Select the column containing the entire names. If your facts begins in cell A1, click on the letter A on the top of the column to pick out the whole column.

Step 3: Go to Data Tab and Select Text to Columns

Go to the Data tab within the Excel ribbon and Click on Text to Column

Step 4: Select Delimiter and Click on Next

In the wizard, pick the option Delimited and click on Next.

Step 5: Choose the Delimiter from the Options and Click on Next

Choose the Delimiter from the options below such as space, comma

You can Split first and last name by space and Separate first and last name by comma depending on your preference. Here we have selected Comma.

Step 6: Choose Column Data Format, Specify the Destination and Click on Finish

Choose the format for the separated data columns. You can both pick the General format or specify the layout for every column via selecting the suitable statistics type and Specify the Destination spot cells in which you need the separated records to be placed. You can pick out to update the existing facts or specify new cells.

Step 7: Preview Results

Pro Tip: Always make a backup before using Text to Columns, as it overwrites existing data by default.

Method 2: How to Separate First and Last Name in Excel with Formulas

Using formulas provides greater flexibility, especially when dealing with middle initials or multiple spaces. Here’s how to split name and surname in Excel using formulas:

Step 1: Open your Excel Spreadsheet

To directly open Google Sheets click here. Start with a spreadsheet where the full names are listed in column A.

Step 2: Enter the Formula

If your full names are in column A starting from cell A2, enter the following formula in cell B2 to extract the first name:

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

Step 3: Enter the Formula

Enter the following formula in cell C2 to extract the last name:

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

Step 4: Drag the Formula

Use the fill handle to drag the formulas down the column and apply the formula to the entire list. This method is especially useful when dealing with large datasets that need to be separated into first and last names.

Pro Tip: Use the TRIM function to clean up extra spaces in names, e.g., =TRIM(A2).

Method 3: How to Split Names in Excel Using Flash Fill

Flash Fill is a quick way to automatically separate first and last names in Excel based on patterns. Here’s how to use it:

Step 1: Open your Excel Spreadsheet

To directly open Google Sheets click here.

Step 2: Enter your Full Name in Column A

Write your Full Name in Column A.

Step 3: Enter Data in Adjacent Columns

Enter the first name of the first entry in cell B2 and the last name in cell C2.

Step 4: Use Flash Fill for First Name

In the next row, press Ctrl + E (Windows) or Cmd + E (Mac) to automatically fill down the first names for the entire column. Do same for Last name (Column C) also.

Step 5: Preview Results

Pro Tip: Flash Fill works best with consistent data patterns.

How to Split Full Name into First, Last, and Middle Name in Excel

If your names are in the First name Middle name Last name format, the below formulas will work a treat. Follow the below steps to Separate name in Excel 2013, 2016 and 2019 with Flash Fill.

Step 1: Enter Full Name in the Cell

Step 2: Use Formulas to Extract First, Middle and Last Name

Below are some Formulas for First name, Middle Name and Last Name

1. For First Name Use the Formula

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

Explanation:

FIND(" ", A2): Finds the position of the first space in the text from cell A2.

LEFT(A2, ...): Extracts all characters from the left side of the cell up to the first space, excluding the space itself.

2. For Middle Name Use the Formula

=MID(A2, FIND(" ", A2) + 1, FIND(" ", A2, FIND(" ", A2) + 1) - FIND(" ", A2) - 1)

Explanation:

FIND(" ", A2: Finds the position of the first space (end of the first name).

FIND(" ", A1, FIND(" ", A2) + 1): Finds the position of the second space (start of the last name).

MID(A2, ..., ...): Extracts the characters between the first and second spaces, which corresponds to the middle name.

3. For Last Name Use the Formula

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

Explanation:

LEN(A2): Calculates the total length of the text in A1.

FIND(" ", A2, FIND(" ", A2) + 1): Finds the position of the second space, which marks the start of the last name.

RIGHT(A2, ...): Extracts characters from the right side of the text, starting from the position of the second space.

Example: In the Below example, we have a name " John Michael Doe"

How to Separate Names in Excel using Find and Replace

Step 1: Copy the names of Name Column and paste it in Column FirstName Column

Copy the full names from the source column (e.g., Column A) and paste them into two separate columns:

• Column B for First Names.
• Column C for Last Names.

Step 2: Open the Find and Replace Tool

Press Ctrl + H (Windows) or Cmd + H (Mac) to open the Find and Replace dialog box.

Step 3: Separate the First Names

Find the Last Name and Extra Text:

In the Find what field, type:

* (space followed by an asterisk symbol)

This will target everything after the first space, including the last name.

Replace with a Blank:

• Leave the Replace with field blank.
• Click Replace All.
• Now, only the first names remain in Column B.

Step 4: Separate the Last Names

Copy Full Names to Column C:

If not already done, paste the full names into Column C (Last Names column).

Find the First Name and Extra Text:

In the Find what field, type:

* (asterisk symbol + space)

This will target everything before the first space, including the first name.

Replace with a Blank:

Leave the Replace with field blank.

Click Replace All.

Now, only the last names remain in Column C.

Step 5: Verify and Adjust

• Review both columns to ensure names are correctly separated.
• Adjust any inconsistencies manually if needed.

Handling Middle Names or Multiple Spaces in Excel

If your data contains middle initials or multiple spaces, you might need to adjust your formulas. Modify the FIND and LEN functions to account for the additional spaces. For example:

=TRIM(LEFT(A1, FIND(" ", A1)))

This formula helps ensure that names with middle initials or additional spaces are handled correctly when separating first and last names in Excel.

Conclusion

Separating first and last names in Excel doesn’t have to be a tedious task. With methods like Text to Columns, formulas, and Flash Fill, you can quickly organize your data and save time for other tasks. Try these techniques on your dataset, and experience how Excel’s powerful tools can simplify your data management processes.