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

Jeffrey_PMO

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?

Carson Penticuff

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

Carson Penticuff

You are attempting reference a second comma, but one will not be present if there are only two contacts. Give this a try:

=IFERROR(RIGHT([Project Support]@row, LEN([Project Support]@row) - FIND(",", [Project Support]@row) - 1), "")

Edited to add the "-1" at the end, though I believe it will ignore the whitespace anyway.

Steve Rohrdanz

Try this.

Formula for Project Support 2: =IF(COUNTM([Project Support]@row) = 2, RIGHT([Project Support]@row, FIND(",", [Project Support]@row) + 2), IF(COUNTM([Project Support]@row) > 2, MID([Project Support]@row, FIND(",", [Project Support]@row) + 2, FIND(",", [Project Support]@row, FIND(",", [Project Support]@row) + 1) - FIND(",", [Project Support]@row) - 2), ""))

Steve

Jeffrey_PMO

https://community.smartsheet.com/discussion/comment/461843#Comment_461843

Thank you Carson for the response. The formula you provided works when there are two contacts but not when there are 1 or 3 contacts.

Jeffrey_PMO

https://community.smartsheet.com/discussion/comment/461847#Comment_461847

Thank you Steve for the response. This was close to working but brings in the last initial and a comma from the first contact.

Carson Penticuff

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

Jeffrey_PMO

https://community.smartsheet.com/discussion/comment/461864#Comment_461864

Thank you Carson, that solved it! I really appreciate your help, spent a few hours on that one!

Steve Rohrdanz

That's interesting because it works properly for me.