How to Alphabetize in Google Sheets by Last Name

Sorting a list of names in Google Sheets that ends up being somewhat of a puzzling order is one of the experiences we all have gone through. Since, by default, Google Sheets using alpha sorting considers the first letter of the first name, this cannot be the preferred sorting method when you need an Alphabetize in Google Sheets by Last Name.

So when you want to arrange a class list or a client database or some contact numbers, the option of sorting by the last name will be a time-saver and frustration-eraser. With this guide, you will learn a step-by-step procedure for alphabetizing names by last name in Google Sheets.

Step 1: Split Full Names into First and Last Name Columns

Since Google Sheets sorts data by the first word in a cell, you need to separate first and last names into different columns before sorting.

1. Select the column containing the full names.
2. Click on Data in the top menu and choose Split text to columns.
3. Google Sheets will automatically split names based on spaces. If not, select Space as the delimiter.
4. Now, you should have first names in one column and last names in another.

💡 Pro Tip: If names include middle initials, the last name might not be in the second column. In such cases, manually adjust entries as needed.

Step 2: Sort the Data by Last Name

Now that last names are in a separate column, you can easily alphabetize the list.

1. Highlight the entire dataset, including the first and last name columns.
2. Click Data > Sort range.
3. Check the box that says Data has a header row (if applicable).
4. Choose the last name column as the sorting column.
5. Select A → Z for ascending order or Z → A for descending order.
6. Click Sort, and your list will now be alphabetized by last name.

Alternative Method: Using a Formula to Extract Last Names

If you want a dynamic method that doesn’t alter your original data, use a formula to extract the last name:

• Use the following formula in a new column to extract the last name:
  =ARRAYFORMULA(RIGHT(A2:A, LEN(A2:A) – FIND(” “, A2:A)))
• Replace A2:A with your actual data range.
• Now, sort using this new column following the steps in Step 2.

FAQs

What if my names have different formats (e.g., “John Doe” vs. “Doe, John”)?

If names are in “Last, First” format, you can split text using a comma as the delimiter instead of space.

Can I sort by last name without splitting the names?

Not easily. You need a formula or manual separation to ensure accuracy.

What happens if some names don’t have last names?

They will be sorted as-is, usually appearing at the top when sorted in ascending order.

How do I revert back to the original order?

Before sorting, add a new column and number the rows. You can then sort by this column to restore the original order.

Can I automate this process?

Yes! Using Google Apps Script, you can create a function that automatically extracts last names and sorts them.

Conclusion

Alphabetize in Google Sheets by Last Name is easier than it seems. By splitting full names into first and last name columns, you can efficiently sort your data without hassle. If you frequently work with large datasets, consider using formulas or scripts to automate the process.