How to Separate First and Last Names in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office 30 30 people found this article helpful

How to Separate First and Last Names in Excel

Easily organize your data

By Luis E. Chavez Saenz Luis E. Chavez Saenz Writer New Jersey Institute of Technology Luis Chavez is a former Lifewire writer and a System Administrator with 5+ years' experience writing about technology for end users. lifewire's editorial guidelines Updated on November 6, 2021 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook

What to Know

Highlight the full names, then go to the Data tab and select Text to Column. Select Delimited, then choose a delimiter set a destination.Alternatively, select an empty cell and use the LEFT function to get the first name and the RIGHT function to get the last name.Or, select an empty cell and type the first name of the first record, then go to Data > Flash Fill. In the next cell, repeat with the last name. This article explains how to separate first and last names in Microsoft Excel. Instructions apply to Microsoft Excel 365, Microsoft Excel 2019, 2016, and 2013.

Separate Names in Excel Using Text to Columns

Excel has multiple functions and features that give you the ability to keep your data organized. For example, you can split first and last names using a feature called Text to Columns:
Open the Excel file that has the data you would like to separate. Select the data you would like to separate, in this case, the list of Full Names. If your data includes headers, do not select them, otherwise, Excel will also attempt to separate the data in the headers. Select the Data tab. Select Text to Column in the ribbon. Select Delimited, then select Next. 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. In our data set, the data is separated with spaces, therefore, we will check the Spaces checkbox as our delimiter. 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. Once you have confirmed your destination, select Finish. Excel will place your data in the destination cells.

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. Open the Excel file that has the data you would like to separate. 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. Enter the formula for the First Name and press Enter. =LEFT(A2,SEARCH(" ",A2)-1) In the next cell, enter the formula to get the Last Name and press Enter. =RIGHT(A2,LEN(A2)-SEARCH(" ",A2)) Select both cells with the formulas. Double-click the bottom right corner of the selected cells. This will extend the formula until the last record of the file.

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. Open the Excel file that has the data you would like to separate. Select a cell where you'd like to list the first names and manually type the first name of the first record. Select the Data tab. Select Flash Fill. Excel will automatically populate the first names on the rest of the records of your file. In the next cell, manually type the last name of the first record of your data. Repeat steps 3 and 4. Excel will automatically populate the last names on the rest of the records of your file. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Combine Two Columns in Excel How to Use the Excel INDEX Function How to Use the ISBLANK Function in Excel How to Combine the ROUND and SUM Functions in Excel Use the Excel RIGHT Function to Extract Characters How to Highlight and Find Duplicates in Google Sheets How to Split Cells in Excel How to Use the Round Function in Excel How to Export Data to Excel How to Use the MONTH Formula in Excel How to Count Data in Selected Cells With Excel's COUNTIF Function How to Find Variance in Excel How to Use the Excel DATE Function How to Calculate Percentage in Excel How to Use the COUNTIF Function in Excel How to Export Data from a Word Form to Excel Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies