How to parse/separate multiple contacts

SteveE

Our project plan has a resource field that can have up to 5 people/contacts assigned to a task. I am looking for a way to scan the field and pars/separate the resources into individual columns (example in red box below). I know it may be a strange request, but for our org, it does make sense. 😉

Here is the formula I tried using, but I don't think CHAR works for contacts:

=IF([Count AR]@row > 5, "More than 5 resources", IF([Count AR]@row = 1, Resources@row, IF([Count AR]@row > 1, LEFT(Resources@row, FIND(CHAR(10), Resources@row)), "No additional resources")))

Is parsing contacts possible? And what am I missing. Any help is (as always) greatly appreciated!

Kelly Moore

Hey @Steve E

Below are the formulas you requested, however, unless your contact names are listed as actual email addresses they will no longer behave as contacts. When parsing 'regular' smartsheet contact names the names will become textstrings. This is a known, unresolved issue.

It appears your COUNTM column is named Resource, not [Count AR] in the screenshot

R1

=IF(Resource@row > 1, LEFT(Resources@row, FIND(",", Resources@row)), Resources@row)

R2

=IF(Resource@row >2, MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 1)) + 1, FIND("~",

SUBSTITUTE(Resources@row, ",","~", 2)) - FIND("~", SUBSTITUTE(Resources@row, ",","~", 1))), IF(Resource@row = 2,

MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 1)) + 1, LEN(Resources@row) - FIND("~",

SUBSTITUTE(Resources@row, ",","~", 1)) + 1)))

R3

=IF(Resource@row >3, MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 2)) + 1, FIND("~",

SUBSTITUTE(Resources@row, ",","~", 3)) - FIND("~", SUBSTITUTE(Resources@row, ",","~", 2))), IF(Resource@row = 3,

MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 2)) + 1, LEN(Resources@row) - FIND("~",

SUBSTITUTE(Resources@row, ",","~", 2)) + 1)))

R4

=IF(Resource@row >4, MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 3)) + 1, FIND("~",

SUBSTITUTE(Resources@row, ",","~", 4)) - FIND("~", SUBSTITUTE(Resources@row, ",","~", 3))), IF(Resource@row = 4,

MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 3)) + 1, LEN(Resources@row) - FIND("~",

SUBSTITUTE(Resources@row, ",","~", 3)) + 1)))

R5

=IF(Resource@row = 5, MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",", "~", 4)) + 1, LEN(Resources@row) - FIND("~", SUBSTITUTE(Resources@row, ",", "~", 4)) + 1))

Kelly

Kelly Moore

Hey @Steve E

Below are the formulas you requested, however, unless your contact names are listed as actual email addresses they will no longer behave as contacts. When parsing 'regular' smartsheet contact names the names will become textstrings. This is a known, unresolved issue.

It appears your COUNTM column is named Resource, not [Count AR] in the screenshot

R1

=IF(Resource@row > 1, LEFT(Resources@row, FIND(",", Resources@row)), Resources@row)

R2

=IF(Resource@row >2, MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 1)) + 1, FIND("~",

SUBSTITUTE(Resources@row, ",","~", 2)) - FIND("~", SUBSTITUTE(Resources@row, ",","~", 1))), IF(Resource@row = 2,

MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 1)) + 1, LEN(Resources@row) - FIND("~",

SUBSTITUTE(Resources@row, ",","~", 1)) + 1)))

R3

=IF(Resource@row >3, MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 2)) + 1, FIND("~",

SUBSTITUTE(Resources@row, ",","~", 3)) - FIND("~", SUBSTITUTE(Resources@row, ",","~", 2))), IF(Resource@row = 3,

MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 2)) + 1, LEN(Resources@row) - FIND("~",

SUBSTITUTE(Resources@row, ",","~", 2)) + 1)))

R4

=IF(Resource@row >4, MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 3)) + 1, FIND("~",

SUBSTITUTE(Resources@row, ",","~", 4)) - FIND("~", SUBSTITUTE(Resources@row, ",","~", 3))), IF(Resource@row = 4,

MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",","~", 3)) + 1, LEN(Resources@row) - FIND("~",

SUBSTITUTE(Resources@row, ",","~", 3)) + 1)))

R5

=IF(Resource@row = 5, MID(Resources@row, FIND("~", SUBSTITUTE(Resources@row, ",", "~", 4)) + 1, LEN(Resources@row) - FIND("~", SUBSTITUTE(Resources@row, ",", "~", 4)) + 1))

Kelly

SteveE

Hi Kelly,

I apologize if I caused any confusion with the misnaming. I created my example to share here, but did not alter the formula I was using in my actual sheet.

I can't thank you enough! 😀

That is exactly what I was looking for. I am studying the formula to make sure I understand how it works as I am not at that level with SS formulas yet.

Steve

Kelly Moore

Hey @SteveE

We use the MID function since we need to parse information from the middle of a textstring. To smartsheet, the cell that contains a bunch of multiselects is just one big textstring. The formula further leverages the ability of the SUBSTITUTION function to designate what occurrence of 'old text' it substitutes. The tilde "~" is a placeholder - it is typically a character that is not found in normal text. So we tell the formula to substitute the tilde with whatever occurrence (in your case 1-4), of the old text you wish substitute. In this case it is the separator between the email addresses. The unseen separator is a comma. So we systematically, one by one, substitute a tilde for the correct comma, then tell the FIND function to look for the substituted tilde.

You will see the names in the appear in the correct column. As mentioned above, unless the original contacts were displayed as actual email addresses the name may no longer function as 'contacts'.

Kelly

SteveE

@Kelly Moore - thank you for the explanation. It makes sense, I just need to work through each formula to understand exactly how the FIND and SUBSTITUTION works. I'll get there!

Would I use a similar approach to extract numbers from a cell? Continuing with the resource example above, I also have a work effort column with numbers of work effort hours for each resource, separated by commas. Using formulas, I found in the community, I came up with below for each of the work effort values. All formulas work as long as the values are single digits. If change to double digit (e.g., 10), WE3, WE4 and WE5 no longer work.

WE1

=LEFT([Work Effort All]@row, FIND(",", [Work Effort All]@row, 1) - 1)

 

WE2

=MID([Work Effort All]@row, FIND(",", [Work Effort All]@row) + 1, FIND(",", [Work Effort All]@row, FIND(",", [Work Effort All]@row) + 2) - FIND(",", [Work Effort All]@row) - 1)

 

WE3

=MID([Work Effort All]@row, FIND(",", [Work Effort All]@row) + 3, FIND(",", [Work Effort All]@row, FIND(",", [Work Effort All]@row) + 3) - FIND(",", [Work Effort All]@row) - 3)

 

WE4

=MID([Work Effort All]@row, FIND(",", [Work Effort All]@row) + 5, FIND(",", [Work Effort All]@row, FIND(",", [Work Effort All]@row) + 4) - FIND(",", [Work Effort All]@row) - 3)

 

WE5

=RIGHT([Work Effort All]@row, LEN([Work Effort All]@row) - (FIND(",", [Work Effort All]@row, FIND(",", [Work Effort All]@row) + 5)))

 

SteveE

@Kelly Moore

I was able to figure the work effort portion out using SUBSTITUTION. Thank you very much for your help and insight!

SteveE

@Kelly Moore - Hi again Kelly and sorry to keep bothering you. I solved my issue with work effort and commas.

I am having an issue with the original parsing of contacts. The formula you provided works and parses the names so I can have each in a unique row cell. However, when there is more than 1 contact, the formula inserts a comma after the name (e.g., John Doe, ). Is it possible to remove that?

Also, I understand they no longer work as contacts, but is there any easy way to convert them back to contacts? It makes creating reports difficult if they are not contacts.

Thanks again for you help!

Kelly Moore

If a comma is inserted after the name then you need to remove one more character from the number of characters that the MID is using. Also, if you need your results to be numbers, and not textstrings that look like numbers, you will have to wrap each formula in a VALUE function. Right now, for example, if your result is 1 it will behave as if you have written 'apple' or some other random word. It is not really a number - yet.

As I mentioned from the beginning, once you parse the names they are no longer contacts. Unless you have a lookup table for all of the names so you can match the name back to an email address, the names are just textstrings. They are no longer contacts and will not become contacts without a lookup. This is the issue.

Kelly