Conditionally Parsing Values from a Multi-select Cell

Michael Reis · 06/21/23

Hi!

I am trying to create a column formula that pulls only multi-select values which contain the string "ML" from a multi-select column.

Here is example output that I am looking for:

In this case, "Templates Used" contains the full list, while "Mini Lesson Templates" should only include values from "Templates Used" which contain the string "ML"

Appreciate your assistance in advance!

Paul Newcome · 06/22/23

The only way to do this would be to parse every option out and then use a JOIN/COLLECT combo to bring back the options you want to keep. There are a number of parsing solutions already out here in the Community.

Michael Reis · 06/22/23

https://community.smartsheet.com/discussion/comment/381719#Comment_381719

Hi @Paul Newcome, I researched the topic for a couple of hours prior to posting the question and was unfortunately unable to find a solution that worked for me. My understanding of the COLLECT formula may be flawed, however.

The way I tried to solve the issue was to have a column formula as follows:

=JOIN(COLLECT([Templates Used]@row, [Templates Used]@row, CONTAINS("ML", [Templates Used]@row)), CHAR(10))

However, this implementation of collect seems to treat the multi-select cell values as one cell value.

Paul Newcome · 06/22/23

The below discussion has some options for parsing out multiple selections from one cell into their own cells. In your case, you already have a delimiter established by using the multi-select column type (which is CHAR(10) = line break), so you will want to use the SUBSTITUTE method.

From there you would use the JOIN/COLLECT combo on all of the cells containing the individually parsed options.

https://community.smartsheet.com/discussion/106300/extract-function-select-data-between-defined-tags

Michael Reis · 06/22/23

https://community.smartsheet.com/discussion/comment/381727#Comment_381727

Hi @Paul Newcome,

I'm not sure if I am misunderstanding how to implement the formula you suggested, but using the following formula:

=MID([Templates Used]@row, FIND("ML_", SUBSTITUTE([Templates Used]@row, CHAR(10), "ML_", 1)) + 1, FIND("ML_", SUBSTITUTE([Templates Used]@row, CHAR(10), "ML_", 2)) - (FIND("ML_", SUBSTITUTE([Templates Used]@row, CHAR(10), "ML_", 1))))

results in a single multi-select value "m"

Applying a JOIN(COLLECT()) as such results in the same:

=JOIN(COLLECT(MID([Templates Used]@row, FIND("ML_", SUBSTITUTE([Templates Used]@row, CHAR(10), "ML_", 1)) + 1, FIND("ML_", SUBSTITUTE([Templates Used]@row, CHAR(10), "ML_", 2)) - (FIND("ML_", SUBSTITUTE([Templates Used]@row, CHAR(10), "ML_", 1)))), [Templates Used]@row, <>""), CHAR(10))

Paul Newcome · 06/22/23

For parsing you would not use "ML_" as a delimiter. As indicated in the linked post, you have to use something that is not found in the string.

For joining, you would not use any MID or SUBSTITUTE functions. You would use a JOIN/COLLECT that pulls together all of the helper cells that have the options individually parsed out. This goes in a separate column from those that contain the parsed data.

Formulas and Functions

Formulas and Functions

Conditionally Parsing Values from a Multi-select Cell

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of values in a multi-select dropdown list

How would I write a formula to locate duplicates in 1 column and the difference in another column.

#NO MATCH result but not for identical formulas