Learn how to extract last name in Excel in multiple ways with a few simple steps. Note these methods work for extracting other types of data as well (such as first names), but these examples will demonstrate the methods using last names.
Many times data in Excel is not in the necessary format and it takes some cleanup. Fortunately there are a multiple easy ways to clean up data with very little effort.
Table of Contents
Extract last name from concatenated first and last name
There are two simple ways to extract the last name from concatenated data within an Excel cell. The first is using a formula to parse the text data and extract the last name. The second is using a build in Excel command called text to columns.
Extract last name using formula
To extract a last name from text data in Excel, a formula using the functions RIGHT, LEN, and SEARCH can be used.
Formula
=RIGHT(text, LEN(text) – SEARCH(” “, text))
Arguments (inputs)
text = the text is the cell containing the concatenated text data of the first and last name
Example
In the example above the RIGHT function selects a defined number of characters from the right hand side of the text in cell B6.
=RIGHT(B6, LEN(B6) – SEARCH(” “, B6))
The number of characters is defined by LEN(B6) – SEARCH(” “, B6). LEN returns the number of characters in the full text, in this case 11 total characters. SEARCH finds the position of the space character (” “), in this case the space is the 7th character. Subtracting the result of SEARCH from the result of LEN provides us with the number of characters in the last name, in this case 4 characters. That value is provided to the RIGHT function, which returns the 4 characters beginning from the right hand side of the text, in this case returning “Gute”.
Extract last name using text to columns
To extract last names from text data using the text to columns command in Excel, follow the steps below:
- Select the cells you want to extract the last names from
- Click the Data ribbon –> Text to Columns
- Leave the Delimited option selected and click Next
- Check the Space delimiter checkbox and uncheck any other delimiter checkboxes. Click Next.
- [Optional] Set the destination for the separated data. Click Finish.
In the example above you can see that the last names were extracted from the customer names in column B, and first names placed in column D and last names in column E. We also see that this method had trouble handling a name with multiple spaces (“Darrin Van Huff” in row 8). In this row, the text to column command split the name into three columns with “Huff” in Column F.
Extract last name using Flash Fill
Flash Fill is an a fantastic time saving command that was added in Excel version 2013. Note, it is only available in Excel 2013 and later versions (Excel 2016, 2019, and Excel 365).
- Type the last name in the cell directly to the right of the first row of the data
- On the Data tab, click the Flash Fill command (or shortcut Ctrl + E).
The last names will be automatically populated down the column.
Extract name from email address
Names can also be easily extracted from email addresses in Excel. These methods work best when email addresses are formatted in commonly used structures, such as using a period between first and last names or using first initial and last name. For example, john.doe@gmail.com or jdoe@gmail.com.
Extract first and last name using formula
To extract a first and name from an firstname.lastname email address in Excel, a formula using the functions LEFT, MID, and SEARCH can be used.
Formula for extracting first name from email
=LEFT(email, SEARCH(“.”, email) – 1)
Arguments (inputs)
email = the email address, either a cell reference or text string
Example
In the example above the LEFT function selects a defined number of characters from the left hand side of the email in cell B6.
=LEFT(B6, SEARCH(“.”, B6) – 1)
The number of characters is defined by SEARCH(“.”, B6) – 1, which finds the position of the period (.) is with the email address, and subtracts 1. In this case SEARCH(“.”, B6) returns 7. Subtracting 1 returns 6. That value is provided to the LEFT function, which returns the 6 characters beginning from the from the left side of the text, in this case returning the first name “claire”.
Formula for extracting last name from email
=MID(email, SEARCH(“.”, email) + 1, SEARCH(“@”, email) – (SEARCH(“.”, email) + 1))
Arguments (inputs)
email = the email address, either a cell reference or text string
Example
In the example above the MID function selects a defined number of characters from the middle of the email in cell B6.
=MID(B6, SEARCH(“.”, B6) + 1, SEARCH(“@”, B6) – (SEARCH(“.”, B6) + 1))
The position which the MID function begins it’s text extraction is defined by second argument, SEARCH(“.”, B6) + 1, which finds the position of the period (.) is with the email address, and adds 1 because the last name begins after the period. In this case SEARCH(“.”, B6) returns 7, and adding 1 returns 8.
The number of characters returned after that position is defined by the third argument, in this case SEARCH(“@”, B6) – (SEARCH(“.”, B6) + 1). The first SEARCH finds the position of the “@” symbol within the email and subtracts the number of characters from the position of the period (.) + 1. In this case the “@” symbol is character 12, and the period is 7, so the formula returns 12 – (7 +1) = 4.
Those values are provided to the MID function, which returns the 4 characters beginning at character 8, in this case returning the last name “gute”.
Extract name from email using Flash Fill
Flash Fill is an a fantastic time saving command that was added in Excel version 2013. Note, it is only available in Excel 2013 and later versions (Excel 2016, 2019, and Excel 365).
- Type the names in the cells directly to the right of the first row of the email address data.
- On the Data tab, click the Flash Fill command (or shortcut Ctrl + E).
The names will be automatically populated down the column, next to the email addresses. If you want first and last names in different columns, you can type the first name and last name in separate cells next to each other and then execute the command and Flash Fill will populate the first and last names in different columns.
