One common task many Excel users have to do is to extract the last name from the full name. While it may seem like an easy task, it can get complicated (especially when you’re dealing with data that is not consistent). In this tutorial, I will show you five different ways to extract the last name from the full name in Excel. I will cover different scenarios, where you only have the first and the last name in the dataset, or the first, middle, and last name. So let’s get started!
Extract Last Name Using Find and Replace
Extracting the last name from a full name essentially means you’re replacing everything before the last name with a blank. And this can easily be done using Find and Replace in Excel (and it takes less than 3 seconds). Below I have a names data set from which I want to extract only the last name. Below are the steps to use Find and Replace to remove everything before the last name: The above steps food remove anything before the last space character, which would leave us with the last names only. The above method would also work in case you have an inconsistent names dataset. For example, if you have names that may or may not have a middle name/initial, or may have a prefix such as Mr or Ms. The only condition is that the last name should be at the end of the name How does this work? In the ‘Find what’ field in the Find and Replace dialog box, I have used an asterisk sign (*) followed by a space character. Asterisk is a wild card character that represents any number of characters in Excel. So when I ask it to find an asterisk followed by the space character, it will find the last space character in the cell and everything before that space character would be considered a part of the asterisk. And when I replace it with a blank, everything before the last space character is removed. Note: For this method to work you need to make sure that there are no trailing spaces in your data. In case there is a space character after the last name, doing the above Find and Replace operation would remove everything from the cell. To make sure there are no trailing spaces, you can use the TRIM formula (read – how to remove trailing spaces in Excel)
Extract Last Name Using Formulas (When you Have Only First and Last name)
Suppose you have a data set as shown below where you have the first name and the last name in column A, and you only want to extract the last name from it. Below is the formula that will do that: Let me quickly explain how this formula works. The above formula uses the FIND function to get the position of the space character in the name. Since the position of the space character would be between the first and the last name, once we have the position of the space character, we can use that to extract everything to the right of it (which would be the last name) But how many characters from the right should it extract? Since we have the position of the space character, we can subtract that value from the length of the name (which is given by the LEN function). This tells us how many characters are there after the space character in the name. And this value is then used in the RIGHT formula to extract the last name from the full name. Note that I have used TRIM(A2) instead of A2 in this formula. This is because there is a possibility that the cells containing the names may have leading, trailing, or double spaces in them. Using the TRIM function makes sure that all these extra spaces are ignored and only one space character between the first name and the last name is considered.
Extract Last Name Using Formulas (When you Have First, Middle, and Last name)
In case you have the first, middle, and last name, the formula becomes a little bit longer. Below I have a data set where I have the first, middle, last name in column A, and I want to extract the last name from it. Here is a formula that can do that: The above function uses the same concept where we have to find out the position of the last space character, and then extract everything to the right of that space character. But since there are two space characters, in this case, the challenge is to find out the position of this second space character. To do this, I have used the below-nested SEARCH function (i.e., Search function within a search function) This part of the formula gives us the position of the second space character, which is then used within the RIGHT formula to extract the last name. Note that this formula would only work when you have a consistent names dataset – i.e., all the names have the first, middle, and last names. In case you have a mix of names where some may have middle names but some may not, this formula is not going to work. If that is the case, use the formula in the next section.
When You Have Incosistent Data (Names with and Without Middle Name)
Below I have a named data set where the format is not consistent – in some cells, we only have the first and last name, and in some cells, there are a middle name and/or prefixes as well. While you can still use excel formulas to extract the last name in this case, the formula we’ll get a little bit complicated. Below is the formula that will extract the last name in this inconsistent data set:
Extract Last Name using Flash Fill
Another really fast way to extract the last name from full names is by using the Flash Fill feature in Excel. Introduced in Excel 2013, Flash Fill works by identifying patterns based on user input and giving you the same results for all the cells in the column. Below I have a data set where I have the names in column A and I want to extract the last name in column B. Here are the steps to do this Flash Fill: Note: You can also get the same option when you go to the Home tab, click on the Fill icon, and then click on Flash Fill The above steps would fill the selected range with the last names from names in Column A. As I mentioned, Flash Fill works by identifying the pattern in one or two cells where you have manually entered the data and then replicates it for all the other select cells. In this example, when I entered “Hans” in cell B2, and then used Flash Fill, it recognized that I’m trying to extract the last word from the text string in column A. So we did the same for all the cells I selected. In case you have a dataset where you have a mix of names with and without a middle name, you can still use Flash Fill to extract the last name. This is because the pattern that the flash fill has identified is to extract the last word from the text string, so it doesn’t matter how many words are there, it will give you the last name in any case. Caution: While the flash fill method has worked perfectly in our case, its accuracy is dependent on how well it can identify the pattern. In some cases, it may not be able to identify the right pattern, so it’s important that you double-check the data to make sure it’s correct. In some cases, you may need to enter the data in two or three cells for flash fill to identify the pattern correctly Compared with the formula method covered above, the flash fill method is a lot faster and more convenient. One big difference however is that the result of the formula method is dynamic, which means that in case you change the original name, the result in the cell that has the formula would also update. In the case of Flash Fill, you will have to repeat the same process again to get the updated data
Extract Last Name Using Power Query
If you want to automate the process of extracting the last names from full names, Power Query is the way to go. For example, if you often have a need to separate names in Excel or to extract the first or last name, you can do that once using Power Query, and the next time you need to do it again, you can simply refresh the query with a single click. Below I have a data set of names and I want to extract the last name from these. Note that I have a mix of names where there are middle names and prefixes in some of the cells. Note: When using Power Query, it is best to convert your data into an Excel Table. Doing this would make it a lot easier to work with Power Query, and also allow you to perform the same steps with a single click the next time you add new data set. To convert your data into an Excel Table, select the data and use the keyboard shortcut Control + T (or Command + T if using Mac) Below are the steps to use Power Query to get the last name from this dataset: The above steps would insert a new worksheet in your workbook that would have the resulting table. Note that in the above example, the first name column has the first name as well as the middle name in some cases. In case you only want the last names, you can delete the first column in Power Query, so it will only give you the last name column as the result in the worksheet. Now if you’re thinking that this is a long method and you’re better off using Flash Fill or formula method instead, you’re probably right. The scenario where Power Query really shines is when you get a file that has the data and you need to do multiple modifications to it (say delete some columns, delete blank rows, extract the last name, etc). The next time you get a new file, you won’t have to repeat all these steps again. simply connect your query to the new file and refresh the query. In such scenarios, Power Query is a far better solution than using flash fill or formulas or even VBA. So these are three different methods that you can use to extract the last name from full name in Excel. Personally, I prefer using the Flash Fill method in most cases, as it’s fast and quite reliable. I hope you found this tutorial useful! Other Excel tutorials you may also like:
How to Shuffle a List of Items/Names in Excel? How to Combine First and Last Name in Excel) How to Sort by the Last Name in Excel Extract Usernames from Email Ids in Excel Separate First and Last Name in Excel (Split Names Using Formulas) How to Extract the First Word from a Text String in Excel
