Extracting text from a multi-select drop down column

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?

Find more posts tagged with

Sheets

Formulas

Workflows in Smartsheet

Accepted answers

All comments

=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))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.