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

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?
Answers
-
Try this:
=IFERROR(MID("," + [Project Support]@row + ",", FIND("!", SUBSTITUTE("," + [Project Support]@row + ",", ",", "!", 1)) + 1, FIND("!", SUBSTITUTE("," + [Project Support]@row + ",", ",", "!", 2)) - (FIND("!", SUBSTITUTE("," + [Project Support]@row + ",", ",", "!", 1)) + 1)), "")
The above will pull the first. Where you see the bold 1 / 2 / 1, you would adjust these numbers to 2 / 3 / 2 for the second, 3 / 4 / 3 for the third, so on and so forth to get as many as you need.
Help Article Resources
Categories
Check out the Formula Handbook template!