Extract Last Name in Excel

by

We often need to work with names in Excel. A lot of times we might need to work with a certain part of names in a list.

For example, we might have a list of full names and need to extract the last names to include in a report.

In this tutorial we show you four ways to extract the last name in Excel:

  • Using the Text-to-columns feature
  • Using the Flash fill feature
  • Using a formula
  • Using Power Query

Using the Text-to-Columns Feature to Extract the Last Name in Excel

The Text-to-columns feature in Excel lets you separate text in a column into different columns based on a delimiter.

A delimiter is a character or symbol separating text in a cell. For example, in a person’s full name, the delimiter is the space character.

This means you can easily use the Text-to-columns tool to separate names into two different columns – one for the first name and another for the last name.

Once you have the first and last names in separate columns, you can go ahead and delete the column containing the first names, so that you are left with just the last names!

Here’s how you can get this done step-by-step:

Let’s say you have the following list of full names:

Full Name Dataset

To extract the last names for every name in the above list, follow the steps below:

  1. Select the list of full names that you want to work with (cell range A2:A7 in our case).
  2. Navigate to Data->Data Tools group->Text to Columns.
Click on Text to Columns
  1. This opens the Convert text to Columns Wizard.
  2. At Step 1, check the radio button next to ‘Delimited’.
Select Delimited
  1. Click Next.
  2. At Step 2, uncheck the box next to ‘Tab’ and check the box next to ‘Space’ (since you want to split the names based on the space character).
Select space as the delimiter
  1. On the right side, there’s a checkbox that says ‘Treat consecutive delimiters as one’. Keep this box checked if there’s a chance your names could have more than one consecutive spaces between them. Checking this option will ensure that Excel trims all the extra space characters.
Check Treat Consecutive delimiter as one
  1. In the preview box at the bottom, you can see how your selected data is going to get split.
  2. Click Next.
  3. At Step 3, you can specify what format you want your extracted columns to have. Simply leave the default General option selected.
Select general
  1. In the input field next to ‘Destination’, you need to specify the reference of the cell where you want your separated columns to begin. Since we want to display the separated columns starting from cell B2, we can specify $B$2 in this field.
  2. Click Finish.
  3. You will now find your names separated into columns B (for first name) and C (for last name).
First and last name have been separated
  1. Since you only need the last name, you can go ahead and remove column B. Simply click on the column header, right-click and select ‘Delete’.

You are now left with the last names extracted from your list of names.

Delete the First namr column

Note: This method works if all names contain only first and last names. If any of the names include a middle name, then the method will not work. However, if all the names in the list contained a first, middle, and last name, then you need to follow the same steps. The only difference is that you would then get the list separated into three columns (one for each name) and would need to delete both the first and middle names.

Also read: Remove Middle Name from Full Name in Excel

Using the Flash Fill Feature to Extract the Last Name in Excel

The Flash fill feature is available in Excel 2013 and later versions.

It’s an intelligent feature that recognizes patterns in your data and automatically fills in columns based on these patterns. 

Here’s how you can use Flash fill to extract the last names from a column of full names:

  1. Click on the first cell of your Last Name column (cell B2 in our case).
  2. Enter the last name for the first row manually.
enter the last name in cell B2
  1. Click on the Flash fill button from the Data tab. You will find it under the ‘Data Tools’ group. Alternatively you could press the shortcut CTRL+E from your keyboard (Cmd+E if you’re on a Mac).
Click on Flash Fill icon in the ribbon

You should now find the rest of the cells of the Last Name column filled automatically with the last names.

All last names are extracted

Note: This method also works quite well in recognizing patterns when you have middle names in certain cells. Moreover, it works great in copying patterns, including capitalization and punctuation. So, even if you have your first and last names separated by commas, the flash fill tool does a great job in detecting the pattern and extracting the last names.

Also read: Extract ZIP Code from Address in Excel

Using a Formula to Extract the Last Name in Excel (When you have only First and Last Names)

Now let us look at another way to extract the last name. This time we are going to use a formula.

The formula involves the RIGHT, LEN, and SEARCH functions.

Before looking at the formula, let us first look at what each of these functions does.

The RIGHT Function

The RIGHT function returns a given number of characters from the right side of a given string.

The syntax for this function is as follows:

=RIGHT(text, num_of_characters)

Here,

  • text is the input string that you want to work on
  • num_of_characters is the number of characters you want to extract from the right side of text.

The LEN Function

This function’s job is simple. It returns the number of characters in a given string.

The syntax for this function is as follows:

=LEN(text)

The function accepts a single parameter, which is the string for which you want to find the number of characters.

The SEARCH Function

The SEARCH function is used to find the location of a given string inside another.

As such, you can use this function to get the location of a space character in a given string too.

The syntax for the SEARCH function is as follows:

=SEARCH (find_text, within_text, [start_num])

Here,

  • find_text is the search string that we want to find.
  • within_text  is the text that we want search in
  • start_num is the position in the text that we want to start searching from. This parameter is optional. If ignored, then it is assumed to be the first position of the text.

Putting Together the RIGHT, LEN and SEARCH Functions to Extract the Last Name

Let us now combine the above three functions into the following formula:

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

Enter the above formula in cell B2 and then copy it down to the rest of the cells. Here’s what we get:

RIGHT formula to extract the last name

As you can see from the above screenshot, the formula simply extracted the text after the space character in each cell, which is nothing but the last name!

Note: Alternatively, you could use the FIND function instead of the SEARCH function and you would still get the same result.

Explanation of the Formula

Let us break down the above formula to understand how it works.

We are going to go layer by layer, starting from the innermost function:

  • SEARCH(“ “,A2)

This finds and returns the position of the space character in the string “Ted Johnson”. It returns the number 4, since the space character is at the 4th position in the string.

  • LEN(A2)-SEARCH(“ “,A2)

This takes the position returned by the SEARCH function and subtracts this from the length of the whole string “Ted Johnson”. This is basically the number of characters in the string after the space character. In other words, it returns the value 11 – 4 = 7.

  • RIGHT(A2, LEN(A2)-SEARCH(“ “,A2))

Finally, the RIGHT function extracts LEN(A2)-SEARCH(“ “,A2) characters from the right side of the string.

In this case, it returns the last 7 characters from the string “Ted Johnson”, which simply comprises the last name “Johnson”!

Note: If there’s a chance your names could have more than one consecutive space between them, you can wrap a TRIM function around this formula to trim out the extra spaces: =TRIM(RIGHT(A2, LEN(A2)-SEARCH(“ “,A2)))

Also read: Switch First and Last Name with Comma in Excel (Flip Names)

Formula to Extract the Last Name (When you Have First, Middle and Last Names)

The above formula will only work when you have first and last names on your list.

However, if your list consists of first, middle, and last names, then your formula will be different.

You will then need to slightly tweak the formula to:

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

Take a look at a screenshot of how this works with a sample dataset consisting of first, middle, and last names:

Formula to extract the last name

Explanation of the Formula

In this case, we used a nested search function to find the location of the last space in the name.

This is because the SEARCH function only returns the location of the first occurrence of the search string.

The inner SEARCH function in our formula returns the location of the first space.

We then used the outer SEARCH function to start looking for the space character, starting its search after the position returned by the inner SEARCH function.

In other words,

  • SEARCH(“ “,A2)+1 returns 4, the position of the first space.
  • SEARCH(” “,A2,SEARCH(” “,A2)+1) starts searching from the 5th position (4+1) and returns the position of the next space (position 12).
  • LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2)+1) then calculates the number of characters in the last name, which 19 – 12=7.
  • And the formula RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2)+1)) returns the last 7 characters, which is “Johnson”.
Also read: Find the Position of a Character in a String in Excel

Formula to Extract the Last Name when the Names are Separated by a Comma

In some places, names are specified in reverse, separated by a comma, as follows:

Full Name Dataset with comma

Here, the last name comes before the first name. Moreover, you have a comma separating the names, followed by space.

To extract the last name in such cases, we need to extract the name from the left. So, we need to use the LEFT function.

The LEFT Function

The LEFT function returns a given number of characters from the left side of the string. Syntax of the function is:

LEFT(text, num_of_characters)

Here,

  • text is the input string that you want to work on
  • num_of_characters is the number of characters you want to extract from the left side of text.

To extract the last name when the names are separated by commas, we use the following formula:

=LEFT(A2,SEARCH(", ",A2)-1)

Let us apply this to our sample list of names:

LEFT function to get the last name

As you can see from the above screenshot, the formula extracted the text before the comma in each cell, which is basically the last name.

Explanation of the Formula

In the above formula, we simply searched for the comma character in the name, found out its position, and extracted all the characters from the start of the name up to the comma.

Let us break down the formula to understand how it works:

  • SEARCH(“, “,A2)

This finds and returns the position of the comma character in the string “Johnson, Ted”. It returns the number 8, since the comma character is at the 8th position in the string (right after the last name).

  • =LEFT(A2,SEARCH(“, “,A2)-1)

This extracts all the characters starting from the left of the string up to one less than the position returned by the SEARCH function. In other words, it returns the first 8 – 1 = 7 characters. This is basically the number of characters in the last name of the string. So the formula returns the last name “Johnson”.

Also read: Extract Last Word in Excel

Using Power Query to Extract the Last Name in Excel 

Power Query is a great tool that helps extract and transform data in Excel.

In newer Excel versions, it is available in your Data tab. However, in older versions, you will need to install it as an add-in.

Power Query has a number of amazing data transformation features, which include quickly splitting and/or merging columns. 

Let us see how we can use Power Query to extract the Last Name from a column full names in Excel:

  1. Before applying Power Query to your data, you need to convert it to an Excel table. For this, simply select your data and press the shortcut CTRL+T. Alternatively, you could navigate to Insert->Table.
Click on Table
  1. This opens the Create Table dialog box. Make sure it shows the correct data range (A1:A7 in our case) and click OK.
Check the range for the table
  1. This will convert your data to an Excel Table.
Data has been converted into an Excel table
  1. Now you can apply Power Query to your data. Select any cell in the table.
  2. From the Data tab, click on the ‘From Table’ button (you will find it under the ‘Get and Transform Data’ group).
Click on Data and then From Table
  1. This opens the Power Query Editor.
Power Query Editor opens
  1. Right-click on the column header of the Names column and select Split Column->By Delimiter. Alternatively, you could simply click on Split Column->By Delimiter from the Home tab at the top of the Query Editor window.
Click on Split column by delimiter
  1. This opens the Split Column by Delimiter dialog box. 
Select space as the delimiter
  1. Make sure the Space option is selected as the delimiter and the radio button next to ‘Each occurrence of the delimiter’ is checked.
  2. Click OK. 
  3. Your column should now be split into two parts – one containing the first name and another containing the last name.
Names data has been split based on space
  1. Double click on the column header for the last name and change the column header name to ‘Last Name’.
Rename the column to Last name
  1. You can also remove the first column by right-clicking on the column header and selecting ‘Remove’.
Remove the first name
  1. From the File tab, click on Close & Load.
Click on Close and Load

You should now find a new sheet in your workbook with a table containing your Last Name column. 

Note: If you want to retain the original column containing the full names, then before step 7, select the Add Column tab in the Power Query Editor and select ‘Duplicate Column’. This will create a duplicate of your original column so that you still have the original names in one column while the other one is split into two.

If you’re thinking ‘why bother going through so many steps with the Power Query method, when the other methods are so much simpler’, you have a point.

However, this method is beneficial in the long run, as you can do this once and add it to your power query data cleaning process, so that you don’t have to keep repeating the action for every new worksheet or workbook.

In this tutorial, we showed you four ways to extract the last name in Excel.

We also showed you how to extend these methods to apply them to cases where you have a first, middle, and last name.

We tried to make the tutorial as detailed as we could. So, we hope you found it helpful.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment