Multi-select Dropdown - Select Starting Number characters From Each Option

TDerathé

Hello,

I am trying to create a helper column that pulls data from one of my Multi-select Dropdown columns. The data I want the helper column to collect is the number portion of each option selected in the Multi-select Dropdown.

I've looked a several posts showing how to pull text strings from multi-select options, but it pulls the entire option as part of the text string, and I am just looking to pull the number from each multi-select dropdown option.

I've also seen some posts explaining how to parse all multi-select options to a single column per option, but I don't want to have to create too many additional columns, and the number of options selected in the multi dropdown can vary.

In this case all multi select options will start with "50" but will have " - " that separates the number portion of the option and its text.

The initial formula I currently have in place is:

=SUBSTITUTE([Object Code]@row, CHAR(10), ", ")

But this pulls all values from the multi-select dropdown column as a text value (separated by commas)

What I want the formula to populate is:

504710, 504810.942, 505410

At most there will be 3 multi-select options selected, but most of the time there will be only 1.

Please let me know if this is something that can be done with just one column formula.

Thank you in advance for your help,

-Thomas Derathé

Toufong Vang

Hi, @TDerathé , the following should help you finish your formula:

The expression below returns the second code.

LEFT(MID([Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99), FIND("-", MID([Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99)) - 1)

The expression below returns the third code.

LEFT(MID(MID([Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99), FIND(CHAR(10), MID([Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99)) + 1, 99), FIND("-", MID(MID([Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99), FIND(CHAR(10), MID([Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99)) + 1, 99)) - 1)

You were on the right track having identified the new line character, CHAR(10), as the delimiter used when multiple options are selected.

When the first CHAR(10) is found, grabbing everything from that position onward (let's call this STRING2) returns the second object code and, potentially, the third.

MID( [Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99)

Finding CHAR(10) in STRING2 and grabbing everything from that position onward returns the third object code.

MID( STRING2 , FIND(CHAR(10), STRING2 )+1 , 99) or

MID(MID([Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99), FIND(CHAR(10), MID([Object Code]@row, FIND(CHAR(10), [Object Code]@row) + 1, 99)) + 1, 99)

Finally, everything to the left of the first "-" in the substrings is the code number you need.

Cheers!