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

TestUser123

Hi Everyone!

I need some assistance with a formula to extract single value from a multi-select drop-down to individual columns. I got most of the process to work but for whatever reason the formula for control 4 column ends up displaying a incorrect number "12." instead of "12.1" and control 5 column displays the whole string of numbers instead of just the 5th selection from the dropdown.

Would anyone happen to know what the correct formula should look like?

Control 4(Hidden) - formula

=IF([Quantity of controls (Hidden)]@row > 5, "more than 5 selected", IF([Quantity of controls (Hidden)]@row = 4, SUBSTITUTE([Mitigating Controls]@row, [Control 1 (Hidden)]@row + CHAR(10) + [Control 2 (Hidden)]@row + CHAR(10) + [Control 3 (Hidden)]@row + CHAR(10), ""), IF([Quantity of controls (Hidden)]@row > 1, LEFT(SUBSTITUTE([Mitigating Controls]@row, [Control 1 (Hidden)]@row + CHAR(10) + [Control 2 (Hidden)]@row + CHAR(10) + [Control 3 (Hidden)]@row, ""), FIND(CHAR(10), SUBSTITUTE([Mitigating Controls]@row, [Control 3 (Hidden)]@row + CHAR(10), ""))), "none selected")))

Control 5(Hidden) - formula

=IF([Quantity of controls (Hidden)]@row > 5, "more than 5 selected", IF([Quantity of controls (Hidden)]@row = 5, SUBSTITUTE([Mitigating Controls]@row, [Control 1 (Hidden)]@row + CHAR(10) + [Control 2 (Hidden)]@row + CHAR(10) + [Control 3 (Hidden)]@row + CHAR(10) + [Control 4(Hidden)]@row + CHAR(10), ""), IF([Quantity of controls (Hidden)]@row > 1, LEFT(SUBSTITUTE([Mitigating Controls]@row, [Control 1 (Hidden)]@row + CHAR(10) + [Control 2 (Hidden)]@row + CHAR(10) + [Control 3 (Hidden)]@row + CHAR(10) + [Control 4(Hidden)]@row, ""), FIND(CHAR(10), SUBSTITUTE([Mitigating Controls]@row, [Control 4(Hidden)]@row + CHAR(10), ""))), "none selected")))

Paul Newcome

Are you basically just trying to parse out the [Mitigating Controls] cell across the individual Control (Hidden) columns?

If so, try this:

=IFERROR(MID(CHAR(10) + [Mitigating Controls] + CHAR(10), FIND("!", SUBSTITUTE(CHAR(10) + [Mitigating Controls] + CHAR(10), CHAR(10), "!", 1)) + 1, FIND("!", SUBSTITUTE(CHAR(10) + [Mitigating Controls] + CHAR(10), CHAR(10), "!", 2)) - (FIND("!", SUBSTITUTE(CHAR(10) + [Mitigating Controls] + CHAR(10), CHAR(10), "!", 1)) + 1)), "")

The above will pull the first entry. To pull the second entry, change the bold numbers to 2, 3, 2. Third entry would need to you adjust the bold numbers to 3, 4, 3, so on and so forth.