Search a string and return multiple results

Hi experts,
I have a formula that searches a string for a keywords, in a string that may include more than one result. It only returns the first instance found. Is it possible to have it continue searching and return all found keywords? Here is what I have currently…. I am looking at JOIN(COLLECT, but not clear how to structure it…. thanks for any help!
=IF(CONTAINS("A-1", [Issue]@row), "Found A1", IF(CONTAINS("A-2", [Issue]@row), "Found A2", IF(CONTAINS("A-3", [Issue]@row), "Found A3", IF(CONTAINS("A-4", [Issue]@row), "Found A4", IF(CONTAINS("A-5", [Issue]@row), "Found A5", "")))))
which returns one value if found… looking to return, for example… "Found A2", "Found A4" to a multi-value dropdown list.
Best Answer
-
Hi there,
I believe you could return each IF(CONTAINS()) statement by itself, and add the results together like so:
=IF(CONTAINS("A-1", [Issue]@row), "Found A1","") + CHAR(10) + IF(CONTAINS("A-2", [Issue]@row), "Found A2","") + CHAR(10) + IF(CONTAINS("A-3", [Issue]@row), "Found A3","") + CHAR(10) + IF(CONTAINS("A-4", [Issue]@row), "Found A4","") + CHAR(10) + IF(CONTAINS("A-5", [Issue]@row), "Found A5", "")
CHAR(10) is the same as "new line" so it should separate the results into their own entries in a multi-select column.
Renée Roberge
Answers
-
Hi there,
I believe you could return each IF(CONTAINS()) statement by itself, and add the results together like so:
=IF(CONTAINS("A-1", [Issue]@row), "Found A1","") + CHAR(10) + IF(CONTAINS("A-2", [Issue]@row), "Found A2","") + CHAR(10) + IF(CONTAINS("A-3", [Issue]@row), "Found A3","") + CHAR(10) + IF(CONTAINS("A-4", [Issue]@row), "Found A4","") + CHAR(10) + IF(CONTAINS("A-5", [Issue]@row), "Found A5", "")
CHAR(10) is the same as "new line" so it should separate the results into their own entries in a multi-select column.
Renée Roberge
-
Thanks so much, Renée- this works!
-
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives