How to parse out drop down selections into separate cells

Hello Community

I created a form with multiple drop down options. I need to parse out those options so each one shows in a separate cell. I figured out the first one but cannot find the correct formula for the second, third cells, etc. Any help is appreciated.

Below is the formula i used to parse out the first item (thanks to Paul H who answered on a different post), which works.

=IF(FIND(CHAR(10), [DIVISION]@row) > 0, LEFT([DIVISION]@row, FIND(CHAR(10), [DIVISION]@row, 1) - 1), [DIVISION]@row)


Tags:

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @Rob N

    The demo solution below was created by modifying a sheet that creates a single contact list from a multiple contact list. (Therefore, it is made to support up to 20 lists.)

    As shown in your formula, we need to find the position of CHAR(10), so we systematically obtain the positions up to 20. ([m#] =COUNTM([Multiple Name]@row) <=20)

    [c0] =0

    [c1] =IF([m#]@row > 1, FIND(CHAR(10), [Multiple Name]@row, [c0]@row + 1))

    [c2] =IF([m#]@row > 2, FIND(CHAR(10), [Multiple Name]@row, [c1]@row + 1))

    Using that position, I use an expression to get each list with the same pattern of expressions.

    [n1] =IF([m#]@row = 1, [Multiple Name]@row, IF([c1]@row > [c0]@row, MID([Multiple Name]@row, [c0]@row + 1, [c1]@row - [c0]@row), IF([c1]@row < [c0]@row, MID([Multiple Name]@row, [c0]@row + 1, LEN([Multiple Name]@row) - [c0]@row + 1))))

    [n2] =IF([c2]@row > [c1]@row, MID([Multiple Name]@row, [c1]@row + 1, [c2]@row - [c1]@row), IF([c2]@row < [c1]@row, MID([Multiple Name]@row, [c1]@row + 1, LEN([Multiple Name]@row) - [c1]@row + 1)))

    [n3] =IF([c3]@row > [c2]@row, MID([Multiple Name]@row, [c2]@row + 1, [c3]@row - [c2]@row), IF([c3]@row < [c2]@row, MID([Multiple Name]@row, [c2]@row + 1, LEN([Multiple Name]@row) - [c2]@row + 1)))

    As you can see, you need to increment the number following to c after [n2].

    You can test the sheet by clicking the URL below, as I published the sheet as editable. If you want a copy of the sheet, please get in touch with me at [email protected].


    Column Formulas

    I omitted c5-c18 and n5-n18.

    https://app.smartsheet.com/b/publish?EQBCT=5f8fd490cb204374806dd8b0a72f72e6


  • Rob N
    Rob N ✭✭

    @jmyzk_cloudsmart_jp thank you for your quick reply and detailed explanation. Unfortunately, I was not able to use your in depth formulas as my smartsheet knowledge is pretty basic. I am learning about M# and following your guidance but I unfortunately have not been able to match it up. I am confident its 100% user error and not your formula. I will let you know once I arrive at the solution. Thank you again

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!