As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Separate First and Last Name in Excel

There are several ways to separate first and last names in Excel. Below are different methods you can use.

Method 1: Using Text to Columns

  1. Open your Excel workbook and navigate to the worksheet with the full names.
  2. Select the column of full names.
  3. Click the Data tab on the ribbon and then click the Text to Columns button.
  4. In the Convert Text to Columns Wizard, select Delimited and click Next.
  5. Choose Space as the delimiter and click Finish.

Now, your first and last names should be in separate columns.

Method 2: Using the LEFT, RIGHT, and LEN Functions

If your data is messy with extraneous characters or spaces, you can use functions to separate first and last names.

  1. Open your Excel workbook and navigate to the worksheet with the full names.
  2. In the first empty column, enter the formula: =LEFT(A1, FIND(" ", A1)-1)
  3. Press Enter and copy the formula down to the last row.
  4. In the second empty column, enter the formula: =RIGHT(A1, LEN(A1)-FIND(" ", A1))
  5. Press Enter and copy the formula down.

Now, your first and last names should be in separate columns.

Method 3: Using the SPLIT Function

For Excel 2016 or later, you can use the SPLIT function.

  1. Open your Excel workbook and navigate to the worksheet with the full names.
  2. In the first empty column, enter the formula: =SPLIT(A1, " ")
  3. Press Enter and copy the formula down.

Now, your first and last names should be in separate columns.

Method 4: Using Flash Fill

For Excel 2013 or later, you can use the Flash Fill feature.

  1. Open your Excel workbook and navigate to the worksheet with the full names.
  2. In the first empty column, enter the first few first names.
  3. In the second empty column, enter the first few last names.
  4. Select the first name column, click the Data tab, and then click Flash Fill.

Excel should automatically fill in the rest of the first and last names.