How to Sort by Last Name in Google Sheets

Sorting in Google Sheets allows you to reshape the data how you want. Sometimes, there are a few situations in which you need to use a custom sorting method because of the data you have.

I have a list of my company’s employees. Some of these names have middle names, and I want to sort this list by last names to organize it alphabetically.

The names are in column A, and I want to add a new column to help with sorting.

The process is quite simple: you need a helper column to get the last names into it and then use those last names to sort the entire data.

As you can use a formula for this, it works as a dynamic method. Whenever you add data, it extracts the last name, and then you can re-sort the data based on those last names.

First, I’d insert a new column next to the name column. In this new column, write a formula to extract the last name. For example, in cell B2, you can write =INDEX(SPLIT(A2, ” “), COUNTA(SPLIT(A2, ” “))).

This formula splits the name into parts and extracts the last part, the last name.

Read Also – Separate Names (First and Last) in Google Sheets

Once you get the last names in the columns, you need to select both columns and sort them using the last column, where you only have the extracted last names.

To understand how this formula works, you need to split it into parts:

• The SPLIT(A2, ” “) part breaks the name into separate words wherever there is a space.
• The COUNTA(SPLIT(A2, ” “)) part counts how many words there are after splitting.
• Finally, the INDEX function uses this count to pick the last word. For example, if A2 has “John Michael Smith,” the formula splits this into “John,” “Michael,” and “Smith,” counts three words, and then returns “Smith” as the last name. This method ensures you always get the last name, no matter how many words are in the full name.

However, there are other ways to sort names by last names in Google Sheets, and we will explore all these methods in detail ahead of this tutorial.

Sort by Last Names without using the Helper Column

And if you want to write a formula where you don’t need to create a helper column to extract the last names and then sort the data.

In that case, you can use the ARRAYFOMRULA and REGEXEXTRACT. The formula will be:

=ARRAYFORMULA(SORT(A2:A11, REGEXEXTRACT(A2:A11, "\s(\w+)$"), TRUE))

If you have full names in column A from the range A2:A11, you can enter this formula in cell B2 to get data sorted by the last names in column B in the range B2:B10.

First, the REGEXEXTRACT function looks at each name in the range A2 to A11 and uses a pattern \s(\w+)$ to find the last word, which we assume is the last name. This pattern finds the word after each name’s last space.

Then, the SORT function takes the original names in A2 to A11 and sorts them based on these extracted last names. The TRUE part of the SORT function tells it to sort the names in ascending (A-Z) order.

Using ARRAYFORMULA, the whole formula is applied to each name in the range at once, giving you a sorted list based on last names in a single formula.

Here is the same formula to sort by last name using multiple-column sorting.

=ARRAYFORMULA(SORT(A2:B11, REGEXEXTRACT(A2:A11, "\s(\w+)$"), TRUE, REGEXEXTRACT(B2:B11, "\s(\w+)$"), TRUE))

Note – Make sure to unhide all the hidden rows before you use any of the methods mentioned here in this tutorial

Write Google App Script for a Custom Function to Sort Data based on the Last Name

You can write a Google App Scrip code to help you create a custom function to get data sorted based on the last name. The below code creates a custom function for you.

/**
 * Sorts a range of names by the last name.
 * 
 * @param {range} range The range of names to sort.
 * @return The sorted range of names.
 * @customfunction
 */
function SORTBYLASTNAME(range) {
  var values = range;
  
  // Extract last names and sort
  values.sort(function(a, b) {
    var lastNameA = getLastName(a[0]);
    var lastNameB = getLastName(b[0]);
    return lastNameA.localeCompare(lastNameB);
  });

  return values;
}

function getLastName(fullName) {
  var nameParts = fullName.trim().split(" ");
  return nameParts[nameParts.length - 1];
}

To use this code, go to Extensions and then Apps Script. Paste the code there and then code the window. After that, reload the Google Sheets tab and enter the function in the worksheet like in the following.

Split Names into Multiple Columns and then Sort the Data

In Google Sheets, an option called “Text to Column” can split a single column into multiple columns based on a delimiter.

Select the column header to select all the names. Then, go to the menu at the top, click on Data, and choose Split text to columns. A small box will appear at the bottom of the column; click on it and select Space as the separator.

This will split the full names into first, middle, and last columns.

Next, the column containing the last names should be the last column created by the split. Select all your data, including the new columns with split names. Return to the menu, click Data, and then Sort range.

But to be very honest, I’d not suggest you use this method if you have uneven first, middle, and last names. When you split it into parts, you get blank cells with no middle names. See the example below.

That is why this method is limited in use: it is only useful when you have names with first and last names.

As I said, sorting in Google Sheets is one of the best ways to reshape your data for further analysis. You can sort data based on dates and use colors to sort the data if you want to.