How to Sort by Last Name in Google Sheets


Often you may want to sort the names in a column in Google Sheets based on last name.

Fortunately this is easy to do and the following step-by-step example shows how to do so.

Step 1: Enter the Names

Suppose we have the following list of names in Google Sheets:

Step 2: Create New Column that Contains Last Name

We can use the following formula to extract the last name from each full name:

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

We can type this formula into cell B2, then drag and fill it down to the remaining cells in column B:

Column B now contains the last name of each full name in column A.

Notice that the formula works whether or not the name contains a middle name.

Step 3: Sort by Last Name

Lastly, we can highlight the range B2:A11, then click the Data tab and then click Sort Range and then click Sort range by column B (A to Z):

The rows will be sorted by last name from A to Z:

Note that you can also delete the column titled Last Name if you don’t actually want to keep a separate column with the last names of each person.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: How to Sort and Ignore Blanks
Google Sheets: How to Sort a Pivot Table
Google Sheets: How to Sort by Date

Leave a Reply

Your email address will not be published. Required fields are marked *