Formula to pull constant number string from variable text strings

Paul Newcome

https://community.smartsheet.com/discussion/comment/414692#Comment_414692

My method using the MID function does work in a stepped fashion and can be used to find the first through the last instance without using the extra [N#] columns and without needing different formulas for different steps or referencing different cells. You only need to reference the multi-select cell with my formula. The formula in my post that you are referring to is actually specifically designed for a multi-select dropdown without any modification necessary other than specifying which instance you want to pull (1, 2, 1 / 2, 3, 2 / 3, 4, 3 / etc.).

Using my method means less unique cell references and as such should improve performance quite a bit since it cuts down on the number of needed columns in addition too the number of unique cell references.

Paul Newcome

@Lucas Rayala It isn't the SUBSTITUTE function that reorders the list. It is because a multi-select field is stored on the back end in alphabetical order. I will have to take a look through my notes to see how I got through this reliably, but I am fairly certain I usually end up converting it to a text string in a text/number column.

Lucas Rayala

https://community.smartsheet.com/discussion/comment/415068#Comment_415068

That makes sense! And that's actually a key reason to convert it prior to parsing it (if even just for clarity's sake). I've parsed these straight from the list before using a CHAR(10) as part of the FIND criteria and the results always came through a little wonky because they weren't in the order I was expecting. It's too bad you can't keep that original order -- I don't seem to see a way, anyway. Even simply reflecting a list, =list@row, puts them in alpha-numeric order.

Paul Newcome

@Lucas Rayala I have always made sure my column properties had the various options listed in alpha-numeric order anyway. Haha. I have only run into it a few times where I had to work with a client created list in the order they wanted.

Lucas Rayala

https://community.smartsheet.com/discussion/comment/415093#Comment_415093

I sometimes have large lists, of which only 2 or 3 items are chosen on a regular basis. In those instance, I usually add those two items to the top (as well as the general list). That makes it easier for the user to grab what they need. I think that's why I've run into this.

Paul Newcome

@Lucas Rayala That makes sense. Usually in those cases I will start them out with a letter or number such as as "01. Option C", "02. Option E", "03. Option A", so on and so forth, but of course that's not always the preference.