How to Split Full Names into First and Last Names in Excel?

If you’re working with a list of full names in Excel and need to separate them into first and last names, there are several simple methods to achieve this. This guide will walk you through a step-by-step process to split full names into first and last names using Excel functions or features.

Step 1: Prepare Your Data

Ensure that the list of full names is arranged in a single column. For example, the names might be in column A, with full names like “John Doe” in each cell.

1. Open the Excel sheet containing your data.
2. Verify that the names are formatted as “First Last” (with a space between them).

Step 2: Use the Text to Columns Feature

Excel’s Text to Columns tool is one of the easiest ways to split names into separate columns.

1. Select the column containing the full names.
2. Go to the Data tab in the ribbon.
3. Click on Text to Columns.

Step 3: Choose Delimited as the Data Type

In the Text to Columns wizard, you’ll specify how Excel should split the data.

1. In the Convert Text to Columns Wizard, choose Delimited and click Next.
2. In the Delimiters section, check the box for Space (since there’s a space between first and last names).
3. Click Next.

Step 4: Select the Destination

Now you can choose where to place the separated names.

1. In the final step of the wizard, specify the destination where the first and last names will appear (e.g., columns B and C for first and last names, respectively).
2. Click Finish to split the full names into separate columns.

Step 5: Use Excel Formulas (Optional)

If you prefer using formulas, you can split names using the LEFT, RIGHT, and FIND functions. This is useful if you don’t want to modify the original data.

In the column where you want the first name (e.g., B2), enter the formula:

=LEFT(A2, FIND(" ", A2) - 1)
This extracts the first name by locating the space and taking the characters before it.

In the column for the last name (e.g., C2), enter the formula:

=RIGHT(A2, LEN(A2) - FIND(" ", A2))
This extracts the last name by calculating the length of the text after the space.

Step 6: Copy the Formulas Down the Column

After entering the formulas, you can apply them to the rest of your list.

1. Select the cell with the first name formula.
2. Drag the fill handle down the column to apply the formula to all rows.
3. Repeat the process for the last name column.

Step 7: Clean Up (Optional)

Once you’ve separated the names, you may want to remove the original full name column or adjust formatting.

1. If you used formulas, you can convert the results to values by copying the cells and using Paste Special > Values.
2. Delete the original column of full names if it’s no longer needed.

Get genuine Office Keys at the lowest prices available!