Multi-select dropdown column to pull single value in another column

IB3
IB3 ✭✭
edited 08/15/22 in Formulas and Functions

Hi Everyone!

I need some assistance with a formula to extract a single value from a multi-select drop-down.

It's a bit annoying as I'm sure that I can use an IF statement, but I was struggling to develop a column formula to use either HAS or CONTAIN. I'm sure it's just early morning brain, preventing me from getting it right.

Thank you all for your assistance.

Tags:

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @IB3

    It should probably be:

    =MID([Live Sessions]@row + CHAR(10), FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 3)) + 1, FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 4)) - FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 3)) - 1)

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @IB3

    For the column pulling the first pc of you would use the below:

    =IFERROR(LEFT([Live Sessions]@row + CHAR(10), FIND(CHAR(10), [Live Sessions]@row + CHAR(10)) - 1), "")

    For the other columns use the below, and increment the number bolded.

    =MID([Live Sessions]@row + CHAR(10), FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 1)) + 1, FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 2)) - FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 1)) - 1)

    =MID([Live Sessions]@row + CHAR(10), FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 2)) + 1, FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 3)) - FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 2)) - 1)

  • IB3
    IB3 ✭✭

    Hi @Leibel Shuchat


    Thank you so much for the quick response!

    I input the formulas as provided. But I ran into a problem with the 4th column.

    I entered the following:

    =MID([Live Sessions]@row + CHAR(10), FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 4)) + 1, FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 3)) - FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 4)) - 1)


    That seemed like the logical next iteration according to the pattern, but I might have missed.

    Thank you again for your help so far!

    Please advise.

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @IB3

    It should probably be:

    =MID([Live Sessions]@row + CHAR(10), FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 3)) + 1, FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 4)) - FIND("~", SUBSTITUTE([Live Sessions]@row + CHAR(10), CHAR(10), "~", 3)) - 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!