How to search cell string for text keyword presence

g2smsht

I'm using Jira Connector, and the Jira Label field in the source Jira project can contain multiple values, which get populated in Smartsheet Label column as comma separated strings for each Jira issue row. I am looking for guidance on how to write a formula to search the cell string for a specific keyword, to take subsequent action on. This formula would be placed in a helper column.

Thanks for any guidance,

g2

Matt Lynn#

@g2smsht
=IF(CONTAINS(<insert keyword in quotes here>, <Ref for range or cell to search>), "True", "False")

Paul Newcome

If you have the need to get really specific such as one of the strings being "IT" and another string being "Visit" or any other instance where one string you are searching for an exact match on could in fact be part of another string, I would suggest first converting the comma separated string into a helper multi-select dropdown column.

=SUBSTITUTE([Original Column]@row, ",", CHAR(10))

Note: You may need to put a space in with that comma that is being swapped out depending on exactly how the data is coming over.

From there you would evaluate the helper column with a HAS function.

=IF(HAS([Helper Column]@row, "IT"), "Yes", "No")

or to count or sum, etc…

=COUNTIFS([Helper Column]:[Helper Column], HAS(@cell, "IT"))

g2smsht

Matt Lynn-PCG thanks so much! this worked.