how to pick a specific item from a multi item dropdown cell

Michael Bull

I have a (Dropdown, Multi select) column, cells with up to 10 items. I figured out how to count them (countm), but I cannot find a function, or figure out a way to pick inidividual items.

EG, cell contains item L001 G02 L003 0L005, in no order or specific length. So countm=4. So how do I extract item1 L001? or item 4 - 0L005

Linking that cell to a text creates a string (I can make something hamfisted if the items start with a letter or especially a specific letter), but the items are separated by \cr or somesuch which I cannot seem to search for with find().

Thanks for your help

Michael Bull

OK , i am learning to Google effectively as well.

CHAR(10) is the \cr character, so I can search fro that in the string ... hamfisted but better than nothing

If there is a real function I would prefere that

David Joyeuse

Hi @Michael Bull

What is it exactly that you want to do with the extraction?

There's no real function to extract an item out of it, but there may be some workaround depending on what you intend to do with it.

If would also be really helpful to have a screenshot or two with dummy data to get a better understanding of your view here.

Michael Bull

Here is how I implemented it. It is extremely text-searchy for something that I think is actually a pretty straighforward problem and could be programmed in a real function very easily.

The multi-entry drop down column is call "Test"

Next column: convert Test into a String called String_Test, based on a condition1 (this is an enabling logic I need, not needed for this thing to work generally)

column [String_Test]=IF([condition1]@row = 0), "", [Test]@row)

Next Column : Len_Test is Length of String_Test

column [Len_Test]=IF([condition1]@row = 1, 0, LEN(String_Test@row)

The next 10 columns look for and store up to 10 occurences of Char(10) in String_Test, using function FIND. The first Find finds CR1 in the whole string, the 2nd FIND starts looking at CR1+1, the 3rd starts looking at CR2+1, the forth at CR3, and so on. Whenever a CR_n FIND returns 0 (ie. there is no more carriage return Char(10)), the CR_n+1 is set to zero as you are at the end of the string.

column CR1: =IF([len_Test]@row = 0, 0, FIND(CHAR(10), [String_Test]@row, 1))

column CR2: =IF([CR1]@row = 0, 0, FIND(CHAR(10), [String_Test]@row, [CR1]@row + 1))

column CR3: =IF([CR2]@row = 0, 0, FIND(CHAR(10), [String_Test]@row, [CR2]@row + 1))

....

So now CR1 contains the position of Char(10) character after item1, CR2 the position of Char(10 after item3....

Item1 is the text between pos1 and length CR1

Item2 is between pos CR1 and length CR2-CR1+1

Item3 is between pos CR2 and length CR3-CR2+1

Now, pull out the items, retrieve entry using Function MID(text, startpos, length), with the following modification/caveat, that,

for entry N, if CR_N-1=0 (means N-1 was already the last entry), return ""

for entry N, if CR_N = 0 (no Char(10) was found in string, ie. entry N is the last, retrieve len_Test@row which Find automatically truncates.

column [Entry1]: =IF([len_Test]@row = 0, "", MID(String_Test@row, 1, IF([CR1]@row = 0, [len_Test]@row, [CR1]@row)))

column [Entry2]: =IF([CR1]@row = 0, "", MID(String_Test@row, [CR1]@row, IF([CR2]@row = 0, [len_Test]@row, [CR2]@row) - [CR1]@row + 1))

column [Entry3]: =IF([CR2]@row = 0, "", MID(String_Test@row, [CR2]@row, IF([CR3]@row = 0, [len_LS]@row, [CR3]@row) - [CR2]@row + 1))

Example

[Test]@row has 2 entries, "Foo" and "Bar".

[String_Test]@row = Foo<CR>Bar (<CR> is the Char(10) character of length 1)

[len_Test]@row = 7

[CR1]@row = 4

[CR2]@row = 0 (only on Char(10) in the string

[Entry1]@row = Bar

[Entry2]@row = Foo

==> So although in the dropdown cell the order was Foo then Bar, this gets reshuffled to alphabetic order on retrieval. Where and why that is (not visible in the cell entry as far as I can see) is a flipping mystery.

I want to bet COUNTM is just counting Char(10) characters in a cell, N Char(10) characters meaning N+1 items in a cell. So retrieving these items in a programmed way should be very easy.

One day when I feel adventurous I will convert the above multi-column monster into a single-cell monster. But it is frustrating that such a simple thing (for a capability of multi-entry dropdowns which really distinguishes smartsheet from a spreadsheet) is not available as a function.

Michael Bull

Small correction 1

• ....
• Next Column : Len_Test is Length of String_Test
• column [Len_Test]=IF([condition1]@row = 0, 0, LEN(String_Test)@row

Small correction 2

• ....
• So now CR1 contains the position of Char(10) character after item1, CR2 the position of Char(10) after item2, CR3 the position of Char(10) after item3..., or 0 if there was no more CR10 (ie you are at the last item. In that case the CR_n are set to zero.
•