Cleaning Name Suffixes with Array Formulas

Have you ever wondered how to sort lists using 2nd word in each cell? Sounds a bit tricky, doesn’t it?

But don’t you worry, Oz du Soleil is here to help. He uses a neat trick to sort out full names by the last names. The approach is interesting and can be modified to achieve many similar task.

Now, let’s explore

bullet step 1Create a List of Suffixes

Some last names may be followed by suffixes such as “Jr.” or “PhD”. It is important to make sure that EXCEL does not pick them up as the last names of an individual. So, the first (and the only manual step) is to create a list of suffixes present in the list of names you want to sort.

Note that suffixes are those which follow the last names with a space in between!

bullet step 2Create a Table of Suffixes

Now, go to Insert > Table and select the list and two more columns on its right.

Let’s name the columns from left to right: New Suffix, Sfx with Space and Count Characters.


Now insert the formula =” “&[@[New Suffix]] in the 2nd column of the table and =LEN([@[Sfx with Space]]) in the 3rd column.

You should be able to see that the column titles correspond with what these formulae are doing.

2014-10-28_17-20-23Detecting a Suffix

Now, create a 6 column table (Insert > Table) with your original data on names in the leftmost column. The columns should be named from left to right as follows: Original Data, Test 1, Count Spaces, Replace, Delimiter and Result.

Go to first cell in Test 1 column. Now apply the formula =OR(RIGHT(x,y)=z) . x, y and z not to be typed! These are tasks to do.


x is selecting the left cell.

y is selecting the Count Characters column from your suffixes table (without the header).

z is selecting the Sfx with Space column from your suffixes table (without the header).

Once you’re done putting in the formula, press Ctrl+Shift+Enter. This should give you an output which looks like the picture on the right.

bGetting to the Last Names

It’s time to speed things up now!

In the Count Spaces column, first cell, enter the following formula:

=LEN([@[Original Data]])-LEN(SUBSTITUTE([@[Original Data]],” “,””))

And press Ctrl+Shift+Enter.

Enter the following formulae using the same method as well:

=IF([@[Test 1]]=TRUE,SUBSTITUTE([@[Original Data]],” “,”^”,[@[Count Spaces]]-1),SUBSTITUTE([@[Original Data]],” “,”^”,[@[Count Spaces]]))



And you are done! You should now have the last names in the rightmost column now.

Now Sort Them!


Get the download

[button size=”large” url=”” text=”Click to download the file that Oz used” target=”” color=”orange” ]

What’s Next?

Use this the next time you need to do some tricky sorting.  Share it.

Jamani Arsalan


You may also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free 24 Excel Tips Guide

Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!