Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Community Champion
    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

  • Community Champion
    edited 11/12/24

    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.

  • 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

  • ✭✭✭✭

    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.

  • ✭✭✭✭

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

  • Community Champion
    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), "")), "")

  • ✭✭✭✭

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

  • That's interesting because it works properly for me.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions