How can I return the nth value from a multiselect dropdown?

In looking through the community posts, I have found information about how to count the number of values selected in a multiselect (COUNTM) and see if a specific value has been selected (HAS), but I haven't found anything to help me parse the values in a multiselect. For example, let's say I have a multiselect with three values selected, I would like to pull out each of the values selected and populate them in separate columns.

One idea I have explored is to use the "FIND" function to identify the delimitator [char(10)]. That can help me get the first value out easily. But then I am finding it challenging to get the rest without a bunch of helper columns that get messy. Also, this is not very generalizable to "n" values.

Something like INDEX() would be great, but that doesn't seem to work (SS treats the whole multiselect as a single entry).

Any thoughts?

Find more posts tagged with

Accepted answers

All comments

Ryan Sides

Hey @Scott Orsey I've tried something similar with multiselect and came to the same conclusion as you. There's no easy way to do it. I used FIND and helper columns, but even then, it's messy.

You can break out your selections in their own checkbox columns which helps with reporting (better than multiselects), but again, not ideal.

Scott Orsey

Thank you! I appreciate the quick reply.

For my application, I first tried the checkbox solution that you mention. I used COLLECT() to grab only those that are "true", then INDEX() to identify the nth value. This solution, unfortunately also got messy because then I needed twice as many columns as options (2 for each possible selection: 1 for the checkbox and 1 for the value that the checkbox represents). It's too bad you can't return the column headers. That would have made things easier. I wanted to explore the multiselect because it would be so much easier to understand down the road.

I am now exploring a similar approach to the checkboxes, but instead of checkboxes I'm looking at using a text/number field that would be populated by the value itself if selected or blank if not. This helps with my INDEX(COLLECT()) function. It works great, but now I need to figure out how to build my form so that it makes sense to the user.

A.Curtin

I know this is old, but in case it helps someone else, here is a formula I use for the nth element in a multi-value column.

Nth Element

Assuming you have a multi-value dropdown column named "S" and you want the 2nd selected element, the formula below will return that element or blank if there isn't one...

=IFERROR(SUBSTITUTE(REPLACE(LEFT(S@row, FIND(CHAR(1), SUBSTITUTE(S@row + CHAR(10), CHAR(10), CHAR(1), 2)) - 1), 1, FIND(CHAR(1), SUBSTITUTE(CHAR(10) + S@row, CHAR(10), CHAR(1), 2)) - 1, ""), CHAR(10), ""), "")

To make it fit your own scenario, swap the three instances of S@row with your own column name and swap out the two instances of the number 2 with the number of the element you want to return (or COUNTM(S@row) if you want the last selected element).

FYI - CHAR(10) is the line feed character that splits the elements and CHAR(1) is just a non-printable character that would never be in the selection values to avoid false positives. You could swap CHAR(1) with "|" or any character you are sure does not exist in any of the element values.

Scott Orsey

Hi,

Thanks for posting this. That's a nice solution and I may revisit my original solution because this would offer the ability to have more "options" in my multiselect. Coincidentally, I just posted a listing today of "easy" fixes that I'm surprised Smartsheet hasn't implemented. Among them is the INDEXM() function that would do the same for multiselects as INDEX does for ranges. See if you agree with these:

https://community.smartsheet.com/discussion/119068/why-not-enhancement-requests-that-smartsheet-should-just-do

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.