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
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!