If cell contains any of "apples", "oranges", "pears" in text string then collect

melimob

Hi, I have a column where I want to basically extract certain words from it without having to create multi helper columns.

If I do a nested IF(OR(contains) statement or an IF(and nested statements it will look for conditions in order but I want to evaluate and return any of the certain words.

I can do this with helper columns and concatenate but wondering if there is another solution to avoid this?

Many thanks

Paul Newcome

If I understand your need correctly, you would add a series of IF statements together (not nesting).

=IF(CONTAINS("Apples", [Column Name]@row), "Apples" + CHAR(10), "") + IF(CONTAINS("Oranges", [Column Name]@row), "Oranges" + CHAR(10), "") + IF(CONTAINS("Pears", [Column Name]@row), "Pears", "")

melimob

do you know what Paul, I even played around with "and(if" and totally missed just stacking the formulas in cell! thank you, will try this!

melimob

works perfectly thanks again

Paul Newcome

Happy to help. 👍️