How to sort names by last name with a formula in Excel
Excel formula
=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1))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.
- Result on the example
- a 8-row list that spills from C2 in
C2 - Works in
- Microsoft 365 only
- Google Sheets
- Different formula, see below
See it work
The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.
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.
=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1))Result{8 rows × 1 col} "Noor Ali", "Mary Ann Brooks", "Lee Chen", "Sam Cole", …
A2:A98 rows × 1 col: "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …A range: 8 cells in one column.
-1-1Makes the value negative.
TEXTAFTER(A2:A9," ",-1){8 rows × 1 col} "Ruiz", "Patel", "Chen", "Ortiz", …TEXTAFTER: Text after a delimiter.- text
A2:A98 rows × 1 col: "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", … - delimiter
" " - instance_num
-1-1
- text
SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1)){8 rows × 1 col} "Noor Ali", "Mary Ann Brooks", "Lee Chen", "Sam Cole", …SORTBY: Sorts a range by other ranges. Spills.- array
A2:A98 rows × 1 col: "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", … - by_array1
TEXTAFTER(A2:A9," ",-1){8 rows × 1 col} "Ruiz", "Patel", "Chen", "Ortiz", …
- array
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(SORT(A2:A9,REGEXEXTRACT(A2:A9,"\S+$"),TRUE)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORTBY(A2:A9,RIGHT(A2:A9,LEN(A2:A9)-FIND("@",SUBSTITUTE(A2:A9," ","@",LEN(A2:A9)-LEN(SUBSTITUTE(A2:A9," ",""))))))Excel only | spills 8 × 1 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sort names by last name with a formula
C2, list the names from A2:A9 sorted by last name, A to Z, keeping each full name together.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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.