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

Scott Orsey · 12/14/22

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?

Ryan Sides · 12/14/22

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 · 12/14/22

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 · 01/26/24

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 · 03/14/24

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:

"Why not?" Enhancement Requests that Smartsheet Should Just Do

Scott Orsey

Mar 14, 2024

I've been a SS user going on five years and am a huge fan and advocate. I value this community, and Smartsheet is, in general, AMAZING. However, I find myself regularly scratching my head about why some seemingly simple enhancements that I've seen suggested by the user community just don't get prioritized. I was talking with my colleague @Lauren Dominique and she suggested taking this to the Forum.
This post is an opportunity to share a few enhancements that have generated the most frustration for me in hopes of commiserating with the larger community and, who knows, maybe geting SS to take a little action. I am confident that these have been suggested multiple times through enhancement requests, so this post is just to ventilate a bit. I've tried to restrict this list to those enhancements that I suspect are reasonably simple. I have a "complex" dream list too, but I'll save that for another day.
1) Function to pull Nth value from a multiselect field. We have COUNTM() that can count how many values have been entered in a multiselect and INDEX() that can pull the Nth value from a range, WHY NOT an INDEXM() function that can return the Nth value from a multiselect?
2) Function that can display text for a hyperlink. We have the ability to create Display Text for a static hyperlink in a cell, WHY NOT have a HYPERLINK() function that allows the hyperlink or display text to be calculated by a formula?
3) Function that converts a value to text in a specified number format. We have the ability to set the display formatting for a column and the VALUE() function can transform text into a number, WHY NOT a TEXT() function that converts a number or date into text using a desired number format?
4) Accept ranges in the SUBSTITUTE function. WHY NOT allow ranges for the "old_text" and "new_text" so that we can swap many old_texts for new_texts without nesting many Substitute functions?
5) Set a Cell Value in an Automation to the value from another cell. WHY NOT? (I acknowledge this could create the risk of infinite automation looping, but that doesn't seem insurmountable). Relatedly, allowing us to do something in a workflow after setting a value would be nice too.
6) Copy a subset of columns to a different sheet. We can move/copy the entire row, WHY NOT let us specify the fields that we want to copy?
7) Automate the ability to promote or demote a row to be a parent/child. We can do this manually, WHY NOT make this an automation option?
8) Smartsheet eliminates extra spaces in a cell making it hard to line up text in multiline text boxes. WHY NOT allow us to pad a cell with spaces if needed?
Feel free to comment or add your own SIMPLE fixes that need attention. I'd love to see your thoughts.

Formulas and Functions

Formulas and Functions

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

Answers

"Why not?" Enhancement Requests that Smartsheet Should Just Do

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA