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?

Find more posts tagged with

Contacts

Formulas

Accepted answers

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

All comments

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:

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.