Split contacts to multiple columns.

abenitez

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!

jmyzk_cloudsmart_jp

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.

https://community.smartsheet.com/discussion/87741/contact-field-listing-multiple-contacts-to-feed-individual-rows-in-a-report

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

https://app.smartsheet.com/b/publish?EQBCT=18ee350bb3094a739dc4023c96e960c6

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

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.

(*)

https://help.smartsheet.com/learning-track/level-1-get-started/columns

As you type, the list will display matching contacts from:

1. Collaborators shared to the sheet
2. Contacts/email addresses assigned to other rows in the sheet
3. Contacts from your Smartsheet Contacts List
4. Contacts from the user management screen

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.)

jmyzk_cloudsmart_jp

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.

https://community.smartsheet.com/discussion/87741/contact-field-listing-multiple-contacts-to-feed-individual-rows-in-a-report

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

https://app.smartsheet.com/b/publish?EQBCT=18ee350bb3094a739dc4023c96e960c6

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.

abenitez

@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!

jmyzk_cloudsmart_jp

@abenitez

Glad I could help! 😁

abenitez

@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.

jmyzk_cloudsmart_jp

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.

(*)

https://help.smartsheet.com/learning-track/level-1-get-started/columns

As you type, the list will display matching contacts from:

1. Collaborators shared to the sheet
2. Contacts/email addresses assigned to other rows in the sheet
3. Contacts from your Smartsheet Contacts List
4. Contacts from the user management screen

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.)

abenitez

Thank you very much!