Multi-select dropdown column to pull single value in another column
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")))
Answers
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.3K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 85 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 302 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!