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

=Chris Palmer · 10/13/22

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?

Michael Culley · 10/13/22

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.

Michael Culley · 10/13/22

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.

Darren Mullen · 10/13/22

@Mr. Chris You'll have to do some Smartsheet gymnastics and use the SUBSTITUTE() or REPLACE() functions.

At a high level you'll want to substitute Firstname, and Lastname, with "" then you'll be left with the title.

Darren Mullen · 10/13/22

@Mr. Chris This video of mine might help you some.

=Chris Palmer · 10/13/22

Thank you @Michael Culley this worked! I was struggling on this for hours.

Best Answer