How to Separate First and Last Names in Excel

Easily organize your data

Split Names

Microsoft Excel has multiple functions and features that gives you the ability to keep your data organized. For example, you can split the first and last name in Excel for greater sorting ability. Keep reading to learn more about how to separate names.

This article includes instructions for Microsoft Excel 365, Microsoft Excel 2019, 2016 and 2013. However, Flash Fill is not available in Microsoft Excel 2013, but is available in all the newer versions of Excel starting from Microsoft Excel 2016.

Separate Names in Excel Using Text to Columns

  1. Open the Excel file that has the data you would like to separate.

    Data File
  2. Select the data you would like to separate, in this case, the list of Full Names.

    List of Data

    If your data includes headers, do not select them, otherwise, Excel will also attempt to separate the data in the headers.

  3. Select the Data tab.

    Data menu
  4. Select Text to Column in the ribbon.

    Text to Column
  5. Select Delimited, then select Next.

    Delimeted
  6. Select the type of delimiter your data has, then select Next. If your delimiter option is not listed, select Other and enter the delimiter you would like to use in the text field provided.

    Delimiter

    In our data set, the data is separated with spaces, therefore, we will check the Spaces checkbox as our delimiter.

  7. By default, Excel will overwrite the existing data. If you don't want your data to be overwritten you'll have to modify the Destination value. Select the Destination field and set a destination.

    Destination
  8. Once you have confirmed your destination, select Finish.

    Finish
  9. Excel will place your data in the destination cells.

    Done

Split First and Last Name Using Excel Formulas

This method is a little more complex, but it also allows more flexibility in terms of separating the data, as you specify exactly what data you will extract using the formula.

In order to achieve the desired data, you'll use left function, the right function, and the search function.

When using formulas, you'll have to use different formulas to split the First Name and Last Name, and it will depend on the format of the original data.

  1. Open the Excel file that has the data you would like to separate.

    Data
  2. Select the cell where you'd like either the First or Last Name. For these steps, our data set is formatted like "First Name + Space + Last Name." Therefore, we will use the LEFT function to get the First Name and the RIGHT function to get the Last Name.

    If your data set is in a different format or has a different delimiter, then you will have to adjust the formula accordingly.

  3. Enter the formula for the First Name and press Enter.

    =LEFT(A2,SEARCH(" ",A2)-1)
    First Name
  4. In the next cell, enter the formula to get the Last Name and press Enter.

    =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
    Last Name Formula
  5. Select both cells with the formulas.

    Select Cells
  6. Double-click the bottom right corner of the selected cells. This will extend the formula until the last record of the file.

    Done

Split Names in Excel Using Flash Fill

This method is probably the simplest of them all, but it is only available in Microsoft Excel 2016 and later.

This method is not supported in Microsoft Excel 2013 or older versions of Excel.

  1. Open the Excel file that has the data you would like to separate.

    Data
  2. Select a cell where you'd like to list the first names and manually type the first name of the first record.

    First Name
  3. Select the Data tab.

    Data menu
  4. Select Flash Fill.

    Flash Fill
  5. Excel will automatically populate the first names on the rest of the records of your file.

    First Name Flash Fill
  6. In the next cell, manually type the last name of the first record of your data.

    Last Name
  7. Repeat steps 3 and 4.

  8. Excel will automatically populate the last names on the rest of the records of your file.

    Last Name Flash Fill