Calculated Column - Last Name from Full Name

Question

I'm trying to write a calculated column formula to return the Last Name from a column that currently has employee's full names. The problem that I am having, is that the full names are stored in a few different formats:

• Dan B. Ferland
• Jake Collen
• Bobby Ray Nion

Any suggestions?

Here is the psudo code that I think would work:

count chars to first space
find space in full name starting at char after first space
if error, just do simple formula to return letters after first space
if no error, return chars from right (all chars - second space char #)

Answer 1

This will most likely be easiest with 2 calculated columns. Column 1 ("Part Name"):

=RIGHT([Full Name],LEN([Full Name])-FIND(" ",[Full Name]))

And Column 2 ("First Name"):

=IF(ISERR(FIND(" ",[Part Name])), [Part Name], RIGHT([Part Name],LEN([Part Name])-FIND(" ",[Part Name])))

IS... functions, RIGHT function, and FIND function.

The first column gets everything to the right of the first space, and the second column checks that looking for another space doesn't cause an error (confirming its existence), then performs the same operation again (or just returns the first column if there is no space).