if Contains question

[Manjari]

Hi All, 

Requesting help in getting the output as second column 

91;#Chocolate;115;#Now;123;#Apple;122;#Mango;	Apple;Mango
91;#Chocolate;123;#Ice cream;36;#Cotton;122;#Mango;	Mango
91;#Chocolate;113;#Now;119;#Muffin;123;#Mango;121;#Pineapple	Mango;Pineapple
91;#Chocolate;114;#Now;123;#Sweets;36;#Apple	Apple

I tried if contains but it does not work. 

see the below formula 

if Contains([Topic], "Apple") then "Apple" else if Contains([Topic], "Mango") then "Mango" else if Contains([Topic], "Mango;121;#Pineapple") then "Mango;Pineapple" else if Contains([Topic], "Apple;122;#Mango") then "Apple;Mango"  else Null() end if

I get this output which is incorrect. not sure where I am going wrong

Apple

Mango

Mango

Apple

[fmvizcaino]

Hi @Manjari ,

I first reordered your conditions to test the complexe ones first and the others later.

I also inserted a 0 in the end of every contains function that is a parameter to be set for case sensitive contains. I.e. not find that Apple is in Pineapple 

Also needed to correct the else if to elseif and end if to endif 

if Contains([Topic], "Mango;121;#Pineapple",0) then "Mango;Pineapple"
elseif Contains([Topic], "Apple;122;#Mango",0) then "Apple;Mango"
elseif Contains([Topic], "Mango",0) then "Mango"
elseif Contains([Topic], "Apple",0) then "Apple"
else Null()
endif

Best,

Fernando Vizcaino

[echuong1 · Accepted solution]

Are you looking to keep only those specific combinations, or all combinations using Apple, Mango, and Pineapple? Would you have additional values you may be looking for?

An alternative method that is a little bit easier is to flag the values you'd like to keep in a list, and then use it as a "keep" criteria. The words you are looking for would be listed in the formula tool once, and then every combination using the words in the list would be kept. That way, it is more dynamic and you don't have to create as many conditional statements.

Let me know if this helps!

[Manjari]

Thank you @echuong1. This worked like a charm. 

Thank you for sharing the workflow too. The helped me a lot.