Multi-select dropdown column to pull single value in another column
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.
Best Answer
-
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
-
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)
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!