Formula to Extract a Full Name and Title, into Separate Columns "First" "Last" "Title"

Mr. Chris
Mr. Chris ✭✭✭✭✭
edited 10/13/22 in Formulas and Functions

I have a long list of full names in the "Full Name" Column.

They need to be broken out into 3 separate columns "First Name" "Last Name" and "Title"

Example shown in the screen shot below.

Is there a formula I can use to extract this for each column?

"First Name" I've been using:

=MID([Full Name]@row, FIND(" ", [Full Name]@row) + 1, FIND(" ", [Full Name]@row, FIND(" ", [Full Name]@row) + 1) - FIND(" ", [Full Name]@row) - 2)

"Last Name" I'm using:

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

These formulas work ok, but some errors still appear.

I'm struggling most with creating a formula for the "Title" column.

Perhaps something that extracts everything to the right after the second comma?

Any thoughts on a better way to do this?


Best Answer

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    edited 10/13/22 Answer ✓

    @Mr. Chris

    I accomplished this by having some helper columns that I hid afterwards.

    So one column would be: (Let's say this new column is called "Helper1")

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

    Then the next column would be "Title"

    =RIGHT([Helper1]@row, LEN([Helper1]) - FIND(", ", [Helper1]))

    Does this work?

    Essentially it's grabbing everything after the second comma.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!