Last Name, First Name

Options
Smartsheet User 99
Smartsheet User 99 ✭✭✭
edited 03/25/22 in Formulas and Functions

Please HELP!!!!!,

I am trying to take someone's full name and turn it into Last Name, First Name.

ex.

ADAMS, JONAH = ADAMS, JONAH

ADAMS, CHRISTOPHE MICHAEL III = ADAMS, CHRISTOPHE

LEE ADAMS, JONAH MICHAEL III = LEE ADAMS, JONAH

I was able to figure out how to pull everything left of the comma, but can't figure out the first name part. I got close but kept running into an issue due to names having multiple spaces or no middle name

Tags:

Best Answer

  • Paul H
    Paul H ✭✭✭✭✭✭
    Answer ✓
    Options

    This seems to work, but I still think it could be simplified

    Last Name =LEFT([Full Name]@row, FIND(",", [Full Name]@row))

    Last Name Removed = =SUBSTITUTE(RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(",", [Full Name]@row) - 1), " ", "!") + "!"

    Extra Names Removed = =LEFT([Last Name Removed]@row, FIND("!", [Last Name Removed]@row) - 1)


    To do it all in a single formula

    Fully Nested = =LEFT([Full Name]@row, FIND(",", [Full Name]@row)) + " " + LEFT(SUBSTITUTE(RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(",", [Full Name]@row) - 1), " ", "!") + "!", FIND("!", SUBSTITUTE(RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(",", [Full Name]@row) - 1), " ", "!") + "!") - 1)

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    Answer ✓
    Options

    This seems to work, but I still think it could be simplified

    Last Name =LEFT([Full Name]@row, FIND(",", [Full Name]@row))

    Last Name Removed = =SUBSTITUTE(RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(",", [Full Name]@row) - 1), " ", "!") + "!"

    Extra Names Removed = =LEFT([Last Name Removed]@row, FIND("!", [Last Name Removed]@row) - 1)


    To do it all in a single formula

    Fully Nested = =LEFT([Full Name]@row, FIND(",", [Full Name]@row)) + " " + LEFT(SUBSTITUTE(RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(",", [Full Name]@row) - 1), " ", "!") + "!", FIND("!", SUBSTITUTE(RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(",", [Full Name]@row) - 1), " ", "!") + "!") - 1)

  • Smartsheet User 99
    Options

    OMG, OMG, OMG!!! it works!!!!!!! Thank you so much!!!!!!!!!!!!!!!!!!!!!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!