Conditionally Parsing Values from a Multi-select Cell

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!

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Paul Newcome

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

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

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

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.