How do I pull the fourth, fifth and sixth name from a multiple-contact column?

Jeffrey_PMO
Jeffrey_PMO ✭✭✭✭

In Smartsheet I have a contact column (Project Support) that can have up to six contacts (first name + last name), for example Jane Doe. I then have six other Test/Number columns that pull the first contact, the second contact, the third contact, the fourth contact, the fifth contact and the sixth contact.

The first contact column isΒ Project Support 1Β and the formula isΒ =LEFT([Project Support]@row, FIND(",", [Project Support]@row + ",") - 1).

The second contact column isΒ Project Support 2Β and the formula isΒ =IFERROR(IF(COUNTM([Project Support]@row) = 2, RIGHT([Project Support]@row, LEN([Project Support]@row) - FIND(",", [Project Support]@row)), IF(COUNTM([Project Support]@row) = 3, MID([Project Support]@row, FIND(",", [Project Support]@row) + 2, LEN([Project Support]@row) - FIND(",", [Project Support]@row) - (LEN([Project Support]@row) - FIND(",", SUBSTITUTE([Project Support]@row, ",", "&", 1))) - 2), "")), "").

The third contact column isΒ Project Support 3Β and the formula isΒ =IF(COUNTM([Project Support]@row) = 3, MID([Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) + 2, LEN([Project Support]@row)), "").

The fourth contact column is Project Support 4 and the formula is =IF(COUNTM([Project Support]@row) >= 4, MID([Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) + 1) + 2, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) + 1) + 1) - FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) + 1) - 2), "").

The fifth contact column is Project Support 5 and the formula is =IF(COUNTM([Project Support]@row) >= 5, MID([Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) + 1) + 1) + 2, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) + 1) + 1) + 1) - FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) + 1) + 1) - 2), "").

The sixth contact column is Project Support 6 and the formula is =IF(COUNTM([Project Support]@row) = 6, MID([Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) + 1) + 1) + 1) + 2, LEN([Project Support]@row)), "").

The formulas work when there are 0, 1, 2 or 3 contacts in theΒ Project SupportΒ column but breaks apart in different ways when there are 4, 5 or 6 contacts. Below is an image that shows that breakage. Any suggestions on what I’m missing?

image.png

image.png

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!