Sort by Last Name in Excel When Names Are in One Column

To sort by last name in Excel when the full name sits in one column, you first need the last name on its own. Add a helper column with a formula such as =TEXTAFTER(A2," ",-1), then sort the table by that column. In Microsoft 365 you can skip the helper column and sort in one formula with SORTBY. This guide shows both ways, plus versions for older Excel and the names that trip up every method.

Why doesn't Excel sort by last name?#

Excel sorts text letter by letter from the left, so "Tom Baker" sorts under T and "Sam Adams" under S. Excel has no idea which part of a name is the surname. You have to give it the last name as a separate value to sort on.

All the examples use this list in A1:A7:

Sorted A to Z as it is, the list starts with Ana Maria Costa and ends with Tom Baker. Sorted by last name, it should run Adams, Baker, Costa, Lin, Shah, Wright.

Method 1: a helper column with TEXTAFTER#

A helper column is an extra column that holds a value you only need for sorting or matching. In B1 type "Last name", and in B2 type:

=TEXTAFTER(A2," ",-1)

TEXTAFTER returns the text after a delimiter, here a space. The -1 means "count from the end", so it splits at the last space. That makes Ana Maria Costa return Costa, not "Maria Costa". Fill the formula down to row 7.

TEXTAFTER needs Microsoft 365 or Excel 2024. The guide to extracting the last word covers it in more detail.

How to sort the table by the helper column#

With the last names in column B, sort as usual:

1. Click any cell in the table.
2. Go to Data > Sort (Windows keys: Alt, A, S, S).
3. Make sure My data has headers is ticked.
4. In Sort by, choose Last name, and in Order, choose A to Z.
5. Click OK.

Every row moves together, so other columns such as email or department stay with the right person. Don't select only column A before sorting, or Excel may sort the names and leave everything else behind. If it asks, choose Expand the selection. The guide to sorting data in Excel covers multi-level sorts.

To also sort people with the same surname by first name, click Add Level and pick a first-name column as the second key.

Method 2: sort by last name in one formula with SORTBY#

SORTBY sorts one range by the values in another, and that other range can be calculated. Click an empty cell such as C2 and type:

=SORTBY(A2:A7,TEXTAFTER(A2:A7," ",-1))

The result spills down six cells: Sam Adams, Tom Baker, Ana Maria Costa, Mei Lin, Priya Shah, Leo Wright. The original list doesn't change, and the sorted copy updates when you edit a name.

To sort a whole table, give SORTBY the full range: =SORTBY(A2:C7,TEXTAFTER(A2:A7," ",-1)). To break ties by first name, add a second key:

=SORTBY(A2:A7,TEXTAFTER(A2:A7," ",-1),1,TEXTBEFORE(A2:A7," "),1)

SORTBY needs Excel 2021 or Microsoft 365, and TEXTAFTER needs Microsoft 365 or Excel 2024. The comparison of SORT vs SORTBY explains why SORT on its own can't do this: SORT can only sort by a column that is already in the range, which here means using the helper column, as in =SORT(A2:B7,2). The SORT function guide lists its arguments.

How to get the last name in older versions of Excel#

Without TEXTAFTER, use FIND to locate the space. FIND returns the position of one piece of text inside another:

=MID(A2,FIND(" ",A2)+1,100)

For "Priya Shah", the space is character 6, so MID returns everything from character 7: Shah. The 100 is just "enough characters to reach the end". The FIND function guide explains the arguments.

This finds the first space, so Ana Maria Costa returns "Maria Costa". When some names have middle names, use this formula instead, which works in every version:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

It replaces each space with 100 spaces, takes the last 100 characters (the last word plus padding), and TRIM removes the padding. It returns Costa. The guide to splitting a full name shows the matching first-name formulas.

Method 3: Flash Fill, no formula#

Flash Fill copies a pattern you type. In B2, type the first last name, Shah, by hand. Then go to Data > Flash Fill (Windows: Ctrl+E). Excel fills the other last names by following your example.

Flash Fill types plain values, not formulas, so it won't update if a name changes later. Check rows with middle names, because Flash Fill guesses the pattern from your examples. If it gets one wrong, correct that cell and run it again.

Names that break every method#

Splitting at the last space is a rule, and real names don't always follow it. Check the results for:

• Two-word surnames such as "Maria de la Cruz" or "Jan van Dijk". The last word is Cruz or Dijk, which may not be how the person sorts their name.
• Suffixes such as "Jr." or "III", which become the "last name".
• Extra spaces, such as two spaces between names or a space at the end. Clean the column with TRIM first, or the last word may come out blank.
• Names already in "Last, First" order. These sort correctly as they are. To flip them, use =TEXTAFTER(A2,", ")&" "&TEXTBEFORE(A2,",").

For a large list, the most reliable fix is to store first and last names in separate columns from the start.

Practise it#

Splitting names is a classic job test task. Practise TEXTBEFORE and TEXTAFTER in a live spreadsheet: you type the formula and it checks it instantly. Then practise FIND and SEARCH for the older approach and practise the SORT function to order the results. Or try the SORTBY exercise on this page first.