Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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 #)
share|improve this question

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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