How to sort by last name in Google Sheets, and keep the list sorted

Google Sheets sorts by the whole cell, so a column of full names ("Ada Lovelace") sorts by first name. To sort by last name you either split the name into two columns or put the last name in a helper column, then sort the range. This page gives both ways, a formula for "Last, First" lists, and a free script that keeps the list in last-name order when names are added. The formulas below were run in a real Google Sheet on 2 November 2026.

1. Names in one cell: Split text to columns, then sort

Copy the name column to a spare column first (the split overwrites cells to its right).
Select the copied cells and choose Data > Split text to columns, with the separator set to Space. "Ada Lovelace" becomes "Ada" and "Lovelace" in two columns.
Select the whole table, choose Data > Sort range > Advanced range sorting options, tick Data has header row, pick the last-name column and A to Z, and click Sort.

Select every column of the table before sorting, not one column, or the names will move away from their emails and phone numbers. This sorts once; the next name you type lands at the bottom.

2. Without splitting: a helper column with the last name

Put a header such as Last name in G1 and this in G2, then fill it down:

=IFERROR(REGEXEXTRACT(TRIM(A2), "[^ ]+$"), "")

It takes the last word of the cell ("Lovelace"). Then use Data > Sort range > Advanced range sorting options on the whole table (A to G), sort by column G A to Z, and add the full name column as a second sort column so equal surnames stay in order.

If the list is written "Last, First" ("Smith, John"), take the text before the comma instead. This version handles both styles in one column:

=IF(ISNUMBER(SEARCH(",", A2)), TRIM(LEFT(A2, SEARCH(",", A2) - 1)), IFERROR(REGEXEXTRACT(TRIM(A2), "[^ ]+$"), ""))

3. A live sorted copy with one formula

On a new tab, this shows columns A and B of the first tab sorted by last name and skips empty rows:

=SORT(FILTER(Sheet1!A2:B, Sheet1!A2:A<>""), ARRAYFORMULA(REGEXEXTRACT(TRIM(FILTER(Sheet1!A2:A, Sheet1!A2:A<>"")), "[^ ]+$")), TRUE)

Checked on a list of eight names it returned Cher, van Gogh, Hopper, Lovelace, Torvalds and Turing in that order (by last word). It is a read-only copy: notes you type next to it do not move with the rows, and you widen A2:B when you add columns.

4. Keep it sorted: a free script

With the helper column from step 2 in column G, this script re-sorts the tab by last name, then full name, every time someone edits it:

Choose Extensions > Apps Script, delete what is in Code.gs and paste the script.
Set the tab name and columns, click Save. onEdit runs on its own when a person edits the tab.

// Keeps a tab sorted by last name (helper column), then by full name.
const SHEET_NAME = 'Sheet1';
const LAST_NAME_COLUMN = 7;   // G, the helper column from step 2
const NAME_COLUMN = 1;        // A, the full name
const HEADER_ROWS = 1;

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== SHEET_NAME) return;
  const rows = sheet.getLastRow() - HEADER_ROWS;
  if (rows < 2) return;
  sheet.getRange(HEADER_ROWS + 1, 1, rows, sheet.getLastColumn())
       .sort([{column: LAST_NAME_COLUMN, ascending: true},
              {column: NAME_COLUMN, ascending: true}]);
}

The helper formula has to exist in every new row, so keep it filled down past the last name. Run it on a copy of the sheet first.

5. The catches

Suffixes and particles. "Zed Adams Jr." gives "Jr." as the last name, and "Vincent van Gogh" sorts under "Gogh", not "van Gogh". Type the surname by hand in the helper cell for those rows.
One-word names ("Cher") work: the whole word is the last name.
Extra spaces are removed by TRIM; a non-breaking space pasted from the web is not, so clean those cells first.
Rows added by other scripts, Zapier or Forms fire no onEdit, so they wait at the bottom until the next edit. The script generator adds triggers for those.
Rows move while people work: someone editing row 5 can see it jump.

6. No code: KeepSorted

KeepSorted is an add-on that keeps the real rows of a range sorted from a sidebar: pick the area and the sort columns (for example last name, then first name), and the header stays on top with formulas and formatting moving with their rows. Free: one sorted area per tab with up to 2 sort columns, no trial clock. Pro ($5/month or $39/year): rows added by people without the add-on or by Google Forms, several tables per tab, up to 5 sort columns and custom orders. Split the names into last and first columns (step 1) and sort by the two.

KeepSorted is built and in testing. It is not in the Google Workspace Marketplace yet. Leave your email and we send one message when you can install it.

You're on the list. We'll email you when KeepSorted is ready.

The formulas and script on this page stay free either way.