How do I pull the second name from a multiple-contact column?

In Smartsheet I have a contact column (Project Support) that can have up to three contacts (first name + last name), for example Jane Doe. I then have three other columns that pull the first contact, the second contact and the third 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(MID([Project Support]@row, FIND(",", [Project Support]@row) + 2, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) - FIND(",", [Project Support]@row) - 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 formulas work when there are 0, 1 or 3 contacts in the Project Support column. But when there are two contacts in the Project Support column, Project Support 2 does not populate. Do you know why?

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I think I finally have it:

    =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), "")), "")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!