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
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
