In my sheet, I have multiple contacts in one cell. I would like to AUTOMATICALLY spread these contacts across multiple columns in the same row, so that each column has one single contact.
The reason I want this is to send approval requests to each contact. (Currently, I can only send approval requests to all the approvers at once because they are all in the same column. When one of them submits an approval, the others cannot).
Limitations: Approvers change depending on other fields on my sheet.
How can I split contacts in one cell into other columns of the same row?
Thank you in advance!
Hi @abenitez
Unfortunately, "there currently isn't a way to parse out a multi-select cell into individual rows/cells automatically", as commented by @Genevieve P. in this discussion.
You can use the Multiple Emails list to send approval requests to each contact, as shown in the image below. That way, you can split the emails to create multiple single contacts.
Regarding splitting the emails into multiple emails, look at the published sheet and click the fx to check the column formula. (This is one of the several ways to parse the multiple dropdown list text.)
[Contact 1] =IF(COUNTM([Multiple Emails 0]@row) < 2, [Multiple Emails 0]@row, LEFT([Multiple Emails 0]@row, FIND(CHAR(10), [Multiple Emails 0]@row)))
[Multile Emails -1]=IF(COUNTM([Multiple Emails 0]@row) < 2, "", SUBSTITUTE([Multiple Emails 0]@row, [Contact 1]@row, ""))
[Contact 2]=IF(COUNTM([Multiple Emails -1]@row) < 2, [Multiple Emails -1]@row, LEFT([Multiple Emails -1]@row, FIND(CHAR(10), [Multiple Emails -1]@row)))
[Multile Emails -2] =IF(COUNTM([Multiple Emails -1]@row) < 2, [Multiple Emails -1]@row, LEFT([Multiple Emails -1]@row, FIND(CHAR(10), [Multiple Emails -1]@row)))
Multiple Contacts List is a Text
For your information, the multiple contacts list is a text value. Making the situation worse, as you can see in the image above, the same demo@cloudsmart.jp contact can have different text values as "demo cloudsmart" and "demo", as shown in the 2nd and 3rd rows.
The image below shows that the same demo@cloudsmart.jp contact can have different values, even if API accesses it. So, you can not split the multiple contacts correctly, even if you use the Smartsheet API.
Hi @abenitez
You can not use the Contact Column type for this solution because Smartsheet automatically converts an email to a name from many candidates. (*)
So, I use the multiple dropdown list instead.
The name of the contact list displayed can not be fixed.
(*)
Columns | Smartsheet Learning Center
As you type, the list will display matching contacts from:
In the first case (shared collaborators), the name that appears is the name that the user has set on their account. (click to see details)
In the second case (Contacts in other rows), if you use "Add New & Type a name" in a contact list cell, the name becomes a candidate.
So, even if you want to enforce a uniform display of contact names in your organization, if your users have the email in their personal Smartsheet Contact List or "Add New & Type a name"with different names, such name is also shown.
If you also need a multiple contact list column, you need to use the Smartsheet API to generate one from emails. (Please note that you can not create multiple contact lists using formulas.)
Hi @abenitez
Unfortunately, "there currently isn't a way to parse out a multi-select cell into individual rows/cells automatically", as commented by @Genevieve P. in this discussion.
You can use the Multiple Emails list to send approval requests to each contact, as shown in the image below. That way, you can split the emails to create multiple single contacts.
Regarding splitting the emails into multiple emails, look at the published sheet and click the fx to check the column formula. (This is one of the several ways to parse the multiple dropdown list text.)
[Contact 1] =IF(COUNTM([Multiple Emails 0]@row) < 2, [Multiple Emails 0]@row, LEFT([Multiple Emails 0]@row, FIND(CHAR(10), [Multiple Emails 0]@row)))
[Multile Emails -1]=IF(COUNTM([Multiple Emails 0]@row) < 2, "", SUBSTITUTE([Multiple Emails 0]@row, [Contact 1]@row, ""))
[Contact 2]=IF(COUNTM([Multiple Emails -1]@row) < 2, [Multiple Emails -1]@row, LEFT([Multiple Emails -1]@row, FIND(CHAR(10), [Multiple Emails -1]@row)))
[Multile Emails -2] =IF(COUNTM([Multiple Emails -1]@row) < 2, [Multiple Emails -1]@row, LEFT([Multiple Emails -1]@row, FIND(CHAR(10), [Multiple Emails -1]@row)))
Multiple Contacts List is a Text
For your information, the multiple contacts list is a text value. Making the situation worse, as you can see in the image above, the same demo@cloudsmart.jp contact can have different text values as "demo cloudsmart" and "demo", as shown in the 2nd and 3rd rows.
The image below shows that the same demo@cloudsmart.jp contact can have different values, even if API accesses it. So, you can not split the multiple contacts correctly, even if you use the Smartsheet API.
@jmyzk_cloudsmart_jp I used your idea with contacts in email form and it worked perfectly. Emails are split and I now have multiple single contacts. Thank you very much!
Glad I could help! π
@jmyzk_cloudsmart_jp Do you know of a better CHAR() number to use in the FIND() function for identifying separate contacts? When I write down an email in my Contact Column, which has a contact list format, it automatically saves it as a name. Therefore the CHAR(10) or CHAR (32) don't work in these cases and the formula doesn't retrieve a contact, but only a first name.
Hi @abenitez
You can not use the Contact Column type for this solution because Smartsheet automatically converts an email to a name from many candidates. (*)
So, I use the multiple dropdown list instead.
The name of the contact list displayed can not be fixed.
(*)
Columns | Smartsheet Learning Center
As you type, the list will display matching contacts from:
In the first case (shared collaborators), the name that appears is the name that the user has set on their account. (click to see details)
In the second case (Contacts in other rows), if you use "Add New & Type a name" in a contact list cell, the name becomes a candidate.
So, even if you want to enforce a uniform display of contact names in your organization, if your users have the email in their personal Smartsheet Contact List or "Add New & Type a name"with different names, such name is also shown.
If you also need a multiple contact list column, you need to use the Smartsheet API to generate one from emails. (Please note that you can not create multiple contact lists using formulas.)
Thank you very much!
