# 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.

## Create 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!

## Create 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.

## Detecting 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.

## Getting 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:

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

Delimiter
=FIND(“^”,[@Replace],1)

Result
=RIGHT([@Replace],LEN([@Replace])-[@Delimiter])

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

## What’s Next?

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

Tags

#### You may also like

December 28, 2015

## What Function Helps You Determine Monthly Loan Payments – Excel Challenge

What Function Helps You Determine Monthly Loan Payments – Excel Challenge

November 22, 2015

## “Everyday” Random Shuffling with Formulas

“Everyday” Random Shuffling with Formulas

December 4, 2015

## VLookup To The Left With The Choose Function – Excel Tips

VLookup To The Left With The Choose Function – Excel Tips

December 22, 2015

## Using Named Formulas In Excel – Like Yesterday()

Using Named Formulas In Excel – Like Yesterday()

June 25, 2015

## Convert Function In Excel – Video Tutorial

Convert Function In Excel – Video Tutorial

October 19, 2015

## Number Of Period Required To Pay Off Debt – Excel Finance Function Challenge

Number Of Period Required To Pay Off Debt – Excel Finance Function Challenge