Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
Word’s Sort feature is OK, up to a point but has its limitations.We humans insist on variations of the simple ‘first and last’ name that confuse a computer.
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 …
- de la ..
- al …
It can also be an issue for people with suffixes on their last name.
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
Of course, 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.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.