Sorting a Word table of names, in Excel

This content is 2 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I’m going to share a secret with you all, dear readers. I really like Microsoft Excel.

There, I’ve said it. Excel may not be the perfect tool in some scenarios (take my example last year, which should almost certainly have used Power BI) but it is the “Swiss Army Knife” of the IT world. End users and IT admins alike have taken the humble spreadsheet programme and twisted it to suit their purposes. I can do so many things with it.

The problem – a table of names in Word

Like a few weeks ago, when I was reviewing a document. The document contained a table with a list of stakeholders. So as not to offend (in a very hierarchical organisation), it had been decided that the names should be in alphabetical order. Except the author had done that by first (given) name, not by last (family/surname) name. Rather than going back and asking them to fix it, I decided I would do so – as it should only take a few minutes.

Word tables are not always that easy to work with, but copying the data into a spreadsheet would let me mangle it, and then paste it back again. Incidentally, Visual Studio Code is also a fantastic text editor and I often use it for complex search and replace operations.

Moving the data to Excel for processing

In this case, I took the two columns from Word (Name, Job Title) and pasted them into Excel. That gives me something like this (massively simplified for the purposes of this blog post – I wouldn’t really go to this much effort for a three-line table! I also made up the names for this example…):

Mark WilsonPrincipal Architect
Brendan ClarkeDirector
Roger QuinnManager

Splitting first and last names

Next, I added a column to the right of Name, and split the Name column, using Excel’s Text to Column wizard and a space as a delimiter. That gave me separate columns for First Name and Last Name.

MarkWilsonPrincipal Architect

Sorting the data

I then sorted the data on Last Names:

MarkWilsonPrincipal Architect

Combining first and last names

Next, I used Excel’s Concatenate function to merge the sorted names back into a single field, for example:


The result is something like this:

BrendanClarkeBrendan ClarkeDirector
RogerQuinnRoger QuinnManager
MarkWilsonMark WilsonPrincipal Architect

Moving the processed data back to Word

Finally, I took the processed data and copied/pasted the combined Name and Job Title text back to Word.

NameJob Role
Brendan ClarkeDirector
Roger QuinnManager
Mark WilsonPrincipal Architect

Featured image by PublicDomainPictures from Pixabay.

One thought on “Sorting a Word table of names, in Excel

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.