GAS – Sheets – Sort by Surname

Nice little equation that I can’t take credit for, can’t remember who provided it in the first place.

Say you have a list of peoples names in the format:

Bob Jones
John Smith
Ann Ableson
Greg Wilson
Steve Dennis
Phil Richards

Google Sheets by default will sort then by the first letter on the left:

Ann Ableson
Bob Jones
Greg Wilson
John Smith
Phil Richards
Steve Dennis

But very often we want to sort by the surname. Now it is easy enough to apply split() and do it that way but this equation will do it for you, producing the results in adjacent cells (assumes your list is in A1:A6):

The equation gets a little unhappy if you have empty rows in your range, and puts them at the top, which if you have a long list of names, might make them disappear off the bottom of the screen. To account for this, you can do a count of the items in the list, then substitute this number in your equation (assumes count formula is in C1:

This will now list as follows:

Ann Ableson
Steve Dennis
Bob Jones
Phil Richards
John Smith
Greg Wilson

Leave a Reply

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