How to Extract Last Name in Excel

This guide will explain multiple methods that you can use to extract last names in Excel.

We will show you how to use Excel’s Text-to-Columns feature, Flash Fill, and Excel formula to retrieve the surname from your name data.

Let’s take a look at a quick example where we will need to extract the last name from a given full name.

Suppose you have a spreadsheet of customer data from your online store. You want to send an email to certain customers to offer them a discount code. 

While drafting the letter, you realize that your dataset contains the full name of the customer rather than separate fields for first name and last name. Since you want to appear more formal, you would like to address the customer by their last name. For example, your customer Jane Smith should be addressed as Ms. Smith instead.

How can we do this in Excel?

There are multiple options to extract the last name in Excel. First, we can use Excel’s Text-to-columns feature. This option will treat spaces between the first and last name as a delimiter and output the separated strings into different columns.

We can also use Excel’s Flash Fill option to extract the customer’s surname. The Flash Fill tool can quickly detect patterns between different columns of data. Users simply have to manually write down the customer’s last name in another column, then use Flash Fill to automatically fill out the rest of the data using similar logic.

Lastly, we can use a combination of Excel functions to obtain the last name. In this guide, we will present a formula that uses the LEN, SEARCH, and RIGHT functions to extract the customer’s surname.

These methods can also be used to obtain the customer’s first name or given name. However, since user input and naming conventions may vary, there are plenty of edge cases where the methods above do not work.

Now that we know some ways to extract the last name in Excel, let’s look at a sample spreadsheet that uses these methods.

 

 

A Real Example of Using Excel to Extract Last Names

Let’s take a look at a real example of the text-to columns function being used in an Excel spreadsheet to extract the last name.

In the table below, we were able to split our name data in Column A into two additional columns.

extract last name in Excel

 

We can create a more dynamic column by using an Excel formula. In the table below, we can change our input in column E and have the Last Name field automatically updated as well.

using formula to extract last name in Excel

 

To get the values in Column C, we just need to use the following formula:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

Let’s explore how this formula works. First, we use the RIGHT function to get a substring of a given name. This makes sense because the last name is naturally a substring of the full name. 

The RIGHT function outputs a certain number of characters from the end of a text string. For example, the formula =RIGHT(“Spreadsheet”,5) will return ‘sheet’ since that string is made up of the last five characters in the given string.

So how do we get the exact number of characters to return? Assuming we only have a single space between the first and last name, we can subtract the length of the full name by the location of the space character to get the length of the last name. This is precisely what the inner formula LEN(A2)-SEARCH(" ",A2) is doing. 

The LEN formula is used to get the number of characters in the string and the SEARCH formula is used to get the exact location of the space in the string.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out these methods in Excel, let’s start using them by following the step-by-step guide in the next section!

 

 

How to Extract Last Name in Excel 

This section will guide you through each step needed to start extracting surnames in Excel. First, let’s learn how to use the text-to-columns feature to split the full name.

  1. First, select the range that holds the full name data. In this example, we’ll select the range A2:A7.
    select list of full names
  2. Next, navigate to the Data tab and click on the Text to Columns icon.
    use Text to Columns feature
  3. A Convert Text to Columns wizard pop-up should now appear. On the first page of the wizard, select the Delimited option. Click Next to go to the next step.
    Text to Columns Wizard
  4. On the next page, select the Space option as a delimiter. Click on Next again.
    use space as delimiter to extract last name in Excel
  5. On the final page, select the cell destination of the output columns. Afterward, click the Finish button.
    select cell destination
  6. The last names should now appear in their own column.
    result of extracting last name in Excel

We can also use the Flash Fill option to retrieve the surname quickly. 

  1. First, manually type in the surname of the first entry in your dataset.
    manually enter last name
  2. While your recent entry is selected, click on the Flash Fill icon in the Data tab.
    use flash fill feature
  3. Excel’s Flash Fill feature can sense patterns between different columns. In the example below, the Flash Fill tool has successfully extracted the last names of customers in the range E3:E9.
    automatically extract last name

Lastly, users who want a more dynamic option can use our last name formula mentioned in the previous section. The benefit of this formula is that it will automatically update when the original full name is modified.

use formula to extract last name in Excel

 

 

This step-by-step guide should be all you need to extract the last name in Excel. Our guide shows how to retrieve the surname from customer data. Users who simply want to clean-up existing data can use the Flash FIll or Text-to-Columns feature. Other users may prefer to use an Excel formula for a more dynamic approach.

Extracting the last name in Excel is just one example of a text operation you can perform in Excel. With so many other Excel functions available, you can surely find one that suits your use case. 

Are you interested in learning more about what Excel can do? Subscribe to our newsletter to find out about the latest Excel guides and tutorials from us. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like