searching for multiple substrings within a column.

Question

Hi all:

I have a column of material description from my ERP system.  I want to filter out those rows where one of several text strings could be present.

To make my live easy, I created a word_flag column, using the formula:

if [Prod_description]
in ("wordA","WordB","WordC"...)
then
1
else
0
endif

However, this does not seem to work.

Am I even using the right tool?

-Tesh.

DataNath · Accepted Answer

Hey @prpatel - the in function works on absolute matches so you’d only get 1s returned if the description is only WordA or WordB or WordC. If you want to check if WordA/B/C is within the description at all then you need to use the contains function.

If contains([Prod_description], ‘WordA’) or contains([Prod_description], ‘WordB’) or contains([Prod_description], ‘WordC’) then 1 else 0 endif

binu_acs · Accepted Answer

@prpatel you should use the contains formula

if Contains([Prod_description],"wordA") OR Contains([Prod_description],"WordB") OR Contains([Prod_description],"WordC")
then
1
else
0
endif

prpatel · Accepted Answer

Thanks....the solution is viable, but I have over 50 of these words to search for.

DataNath · Accepted Answer

@prpatel you could also build a lookup table and use the find and replace tool, appending the flag if it finds a match. All you’d have to do is type the 50 words out then and configure the tool rather than all of them + the contains functions etc