# Calculated Column - Last Name from Full Name

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 #)
``````
-

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])))
``````

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

-
Thank you Stuart! I ended up using this as my final formula: `=IF(ISERROR(FIND(" ",[Full Name],FIND(" ",[Full Name])+1)),RIGHT([Full Name],LEN([Full Name])-FIND(" ",[Full Name])),RIGHT([Full Name],LEN([Full Name])-FIND(" ",[Full Name],FIND(" ",[Full Name])+1)))` – Josh Oct 13 '11 at 21:40
No worries. :) I was trying to avoid the bloated formula problem with the two columns, but it's purely aesthetic. Remember to tick the 'Accepted answer' button on the left; it makes a small simulated kitten purr somewhere in the Stack Exchange server farm. – Stuart Pegg Oct 14 '11 at 7:21