I use the following formula to bring two columns, First Name and Last Name into one column.

Concatenate Text text_value1 + text_value2 Join two text values together. Smith, John John Smith. I use this formula to merge a first name and last name from two different columns into one. Does anyone out there have a solution to reverse that process? See my example in the attached PDF.

• ✭✭✭✭✭✭

You could use a formula to split them based on the space using LEFT/RIGHT and FIND and LEN. However, there will be names (such as the last one in your example) that consist of three words. How should they be handled?

This will return the text up to the first space

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

This will return everything after the first space

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

Here is an example.

KPH - Formula worked fantastic. Thank you so much for the help. You get the gold star!!

Dennis

• ✭✭✭✭✭✭

You could use a formula to split them based on the space using LEFT/RIGHT and FIND and LEN. However, there will be names (such as the last one in your example) that consist of three words. How should they be handled?

This will return the text up to the first space

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

This will return everything after the first space

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

Here is an example.

• Fantastic response. I have been researching similar formulas and I think yours makes a bit more sense. I will give it a try. As far as three words - First, Middle and Last Name only the first and last are needed. I can simply delete the middle name as I have those in a different sheet.

KPH - Formula worked fantastic. Thank you so much for the help. You get the gold star!!

Dennis

• ✭✭✭✭✭✭

Great, glad I could help (although it looks like you have yourself the gold star 🤣).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!