# How to sort names by last name with a formula in Excel

Sort by the text after the last space: `=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1))`. The -1 counts from the end, so middle names don't get in the way.

**Excel:** `=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1))` (Microsoft 365 only)

**Google Sheets:** `=ARRAYFORMULA(SORT(A2:A9,REGEXEXTRACT(A2:A9,"\S+$"),TRUE))`

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Name |  | By last name |
| Ana Ruiz |  |  |
| Raj Patel |  |  |
| Lee Chen |  |  |
| Kim Ortiz |  |  |
| Mary Ann Brooks |  |  |
| Sam Cole |  |  |
| Eve Lund |  |  |
| Noor Ali |  |  |

Formula in C2: `=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1))`

Result (spills 8 × 1): Noor Ali; Mary Ann Brooks; Lee Chen; Sam Cole; Eve Lund; Kim Ortiz; Raj Patel; Ana Ruiz

## How it works

TEXTAFTER with an instance number of `-1` returns the text after the last space, which is the last name even for "Mary Ann Brooks". SORTBY sorts the full names by those last names without showing them.

Google Sheets has neither TEXTAFTER nor SORTBY, so its version pulls the last word with REGEXEXTRACT (`\S+$` means the run of non-spaces at the end) and passes it to SORT as the sort column.

## Other ways

- `=SORTBY(A2:A9,RIGHT(A2:A9,LEN(A2:A9)-FIND("@",SUBSTITUTE(A2:A9," ","@",LEN(A2:A9)-LEN(SUBSTITUTE(A2:A9," ",""))))))`: For Excel 2021, which has no TEXTAFTER: the last space is swapped for @, found with FIND, and RIGHT takes everything after it.

## Common mistakes

- Suffixes such as "Jr." become the "last name". Put suffixes in their own column, or clean them out first.
- A trailing space makes TEXTAFTER return an empty string, which sorts first. TRIM the names: `TEXTAFTER(TRIM(A2:A9)," ",-1)`.
- In older versions without SORTBY, add a helper column with the last name, then sort the table by that column with Data > Sort.

## Questions

**How do I break ties between people with the same last name?**

Add the first name as a second key: `=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1),1,TEXTBEFORE(A2:A9," "),1)`.

**How do I show the name as "Last, First"?**

Rebuild it: `=TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1)`.

**How do I sort Z to A?**

Add -1 as the sort order: `=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1),-1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sort-names-by-last-name