searching for multiple substrings within a column.

[prpatel]

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 solution]

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

[binuacs · Accepted solution]

@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 solution]

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

[DataNath · Accepted solution]

@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

[Sameer_Kanade8810]

Hey Did you get the solution to this 

[prpatel]

I did.  I used the find Replace tool.

I created a text input tool that holds these 50 words that I am searching for in 1 column and then another is a flag column with the number 1 in it.

I then use the find Replace tool.  find within field is the field that I am searching in, find value is the column that has the 50 words, then I append the flag column to the records.

try that.

[Sameer_Kanade8810]

Thank you for your help let me try it.