Dropdown menu & how to separate results

I have a dropdown menu to select a choice of up to 19 out of a possible 42 items, each item selected needs to be separated into its own column as each selection is mapped. The way the mapping has been set up is the first selection in dropdown needs to be at position 1 even if it is the 4th option and the second selection needs to be at position 2 even if it is the 6th on the dropdown options for example. An added complication is that each of the 42 selections fall under 1 of 3 categories which are also mapped, (NB a selection for 1 row can be a different category to the same selection in a different row). There are a further 2 column for each selection.

I was planning on using an automation as I know if I have "Beans" selected then I can automatically fill in the Category, and other 2 columns based on that selection.

The roadblock I hit is how to then do this for selection 2, 3 and so on - is there a better option or do I need to come up with a more complete / complex automation with additional conditions?

Any help advice most welcome.

PS I have tried HAS Formula which again works for 1st column but I don't know how to make it work for second if the first is the 4th option for example especially as in a different row the selections are different…….I have also tried helper columns with Join, collect and the AI formulas to return the first selection etc do not work!

Find more posts tagged with

Formulas

functionality

Automations

Multi-Select Dropdown

Accepted answers

Paul Newcome

It is because you changed the "+ 1" to "+2".

In my thread above, I only mentioned changing the numbers within the SUBSTITUTE functions. If you include all numbers, they would go

1/1/2/1/1

2/1/3/2/1

3/1/4/3/1

4/1/5/4/1

The second and last 1 should always be a 1. The only numbers thaqt should be changed are the ones inside of the SUBSTITUTE functions bolded in my last comment.

All comments

Adam Murphy

Is it possible to share a screenshot or two to help visualize this and show what you have and also what you want it to do (mocked up manually)? Be sure to remove any sensitive data/information.

Paul Newcome

If you just need them parsed out, first insert a helper text/number column called "Selection String" and insert this column formula:

="~" + SUBSTITUTE([Dropdown Column]@row, CHAR(10), "~") + "~"

Then in the first column that will hold the parsed selections:

=IFERROR(MID([Selection String]@row, FIND("!", SUBSTITUTE([Selection String]@row, "~", "!", 1)) + 1, FIND("!", SUBSTITUTE([Selection String]@row, "~", "!", 2)) - (FIND("!", SUBSTITUTE([Selection String]@row, "~", "!", 1)) + 1)), "")

In the above you will see three SUBSTITUTE functions that end with a number (the number is just after the "!" within the function). Reading from left to right, these numbers are 1/2/1.

To modify this formula to pull the second, third, etc. selection made, you would adjust these numbers so that the 1s are the selection number and the 2 is basically the selection number plus one.

So to pull the second, you would use 2/3/2. Third selection would be pulled with 3/4/3, so on and so forth.

CTops

@Paul Newcome This almost works but seems to "cut off" the first letter in the selection when parsed into the individual cell?!

The first one works but when I changed your formula to the following I lose the first character:

=IFERROR(MID([Selection String]@row , FIND("! ", SUBSTITUTE([Selection String]@row , "~", "! ", 2)) + 2, FIND("!", SUBSTITUTE([Selection String]@row , "~", "! ", 3)) - (FIND("!", SUBSTITUTE([Selection String]@row , "~", "!", 2)) + 2)), "")

Have I done something wrong?

Paul Newcome

It is because you changed the "+ 1" to "+2".

In my thread above, I only mentioned changing the numbers within the SUBSTITUTE functions. If you include all numbers, they would go

1/1/2/1/1

2/1/3/2/1

3/1/4/3/1

4/1/5/4/1

The second and last 1 should always be a 1. The only numbers thaqt should be changed are the ones inside of the SUBSTITUTE functions bolded in my last comment.

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.