Limitations of Word Sort feature for name sorting
Microsoft Word’s Sort feature is OK but has its limitations. We humans insist on variations of the simple ‘first and last’ name that confuse a computer. Here’s some name sorting traps and workarounds.
For short lists, it’s probably enough to do a Word surname sort then manually adjust the list for any anomalies.
Longer lists need the power of Excel to manage.
Multi-word last name problems
Sorting by surname is easy if all the last names are single words but if we add some other surnames the sorting gets all messed up.
Word has no way of knowing that multi-word surnames should be treated as a single word.
- Van der …
- Von…
- de la ..
- al …
Surname suffixes
It can also be an issue for people with suffixes on their last name.
- Jr.
- 2nd
- 3rd
Middle Name problems
It’s a similar problem with people who commonly use a middle name while most of the list are just two words:
- Sarah Michelle Gellar
- Edwin Buzz Aldrin
Sorting in Excel
Excel also has sorting features but it also has better ways to split up names.
Flash Fill not only splits up text into different elements but it also learns as you make changes. For example, if you correct a ‘Von …’ to add it to the surname field, Flash Fill will do the same for all other Von names.
Use Flash Fill to separate names into first & last components (or first, middle, last) then sort the separated list before copying into Word.
Here’s an example with Col A the original full names.
Col B and C are those names split into First and Last by Flash Fill with some hybrid magic to fix the naming issues mentioned above.
Use Data | Sort to reorder the table to sort by last name then first name (see at the bottom ‘Liesl’ is before ‘Maria’).
Finally, copy column A into Word as a sorted list.
You could do a similar thing with a Word table but Word doesn’t have Flash Fill. All the parsing of names into separate columns is done separately.
Sort a list of names by surname or more in Word
Save a sorting order for Word tables
Watch your sort and other Excel traps for the unwary
Quick Excel list sorting and filter buttons