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…):

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.

Sorting the data

I then sorted the data on Last Names:

Combining first and last names

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

=CONCATENATE(A1," ",B1)

The result is something like this:

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.

Featured image by PublicDomainPictures from Pixabay.