Search a string and return multiple results

g2smsht

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.

rrenee

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.

rrenee

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.

g2smsht

Thanks so much, Renée- this works!

rrenee

I'm glad to hear it! ☺️

https://community.smartsheet.com/discussion/comment/466599#Comment_466599