Extracting text from a multi-select drop down column

Jayy S.

I have an intake sheet where one of the fields is a multi-select drop down list. The values in the list are NUMBERS; TEST NAME.

I want to be able to parse out just the number into a separate column in the intake sheet as someone submits it, so there'll be a populated column of Test Code Intake that has the NUMBERS; TEST NAME and then a column with formula to populate it only with NUMBERS. Then the whole row will be copied into a consolidated sheet via automation (which I had set up already).

Is there a way this can be achieved?

=Chris Palmer

Hello @Jayy S.

Is this what you're going for?

=LEFT([Column Name]@row, FIND(";", [Column Name]@row) - 1)

Jayy S.

@=Chris Palmer

No, I tried that and it gave an error. Could it be because it's a multi-select column type?

=Chris Palmer

I was able to get this formula to work on a multiselect dropdown column.

=IF(CONTAINS("0013007", [Dropdown Column]@row), "0013007", "") +
IF(CONTAINS("0013008", [Dropdown Column]@row), " 0013008", "") +
IF(CONTAINS("0013009", [Dropdown Column]@row), " 0013009", "")

Jayy S.

@=Chris Palmer Thanks, but I really need a much simpler formula that can be used as a column formula. I can't do this formula and include 3000+ IF statements for every single test codes there are and there will be.

Leibel S

@Jayy S.

One approach here is to make a separate sheet that has a list of these numbers.

you can then make your column formula something like this:

=JOIN(COLLECT({List of numbers},{List of numbers},CONTAINS(@cell,[Dropdown Column]@row)),CHAR(10))