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!
Answers
-
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.
-
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.
-
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.
-
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))
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!