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

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

  • 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
    Commented Oct 13, 2011 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.
    – Stu Pegg
    Commented Oct 14, 2011 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.