I do not know how @nb solved the problem, but I offer a solution to the problem as described with COUNTIF. There are only a handful of functions that interpret strings as regular expressions, and COUNTIF is one of them. So, treating 1 as a TRUE value, and 0 as a FALSE value:
A B
Item Class
apples1 =IF(COUNTIF(A2, ".*apple.*"), "apples", IF(COUNTIF(A2, ".*pear.*"), "pears", "other")
apples2 =IF(COUNTIF(A3, ".*apple.*"), "apples", IF(COUNTIF(A3, ".*pear.*"), "pears", "other")
apples3 =IF(COUNTIF(A4, ".*apple.*"), "apples", IF(COUNTIF(A4, ".*pear.*"), "pears", "other")
Pear A =IF(COUNTIF(A5, ".*apple.*"), "apples", IF(COUNTIF(A5, ".*pear.*"), "pears", "other")
Pear B =IF(COUNTIF(A6, ".*apple.*"), "apples", IF(COUNTIF(A6, ".*pear.*"), "pears", "other")
Melon =IF(COUNTIF(A7, ".*apple.*"), "apples", IF(COUNTIF(A7, ".*pear.*"), "pears", "other")
The basic IF statement format is TEST, TRUE_ACTION, FALSE_ACTION
. Since Calc doesn’t care if we use multiple lines in the formula, we can break it up into a more manageable version with nested IF functions. Consider:
=IF(TEST1, TRUE_TEST1,
IF(TEST2, TRUE_TEST2,
IF(TEST3, TRUE_TEST3, ... )))
This makes writing the above formula more approachable, in, say, a text editor (Notepad, Gedit, TextWrangler, etc.). For example:
=IF(COUNTIF(A2, ".*apple.*"), "ANSWER IF CONTAINS APPLES",
IF(COUNTIF(A2, ".*pear.*"), "ANSWER IF CONTAINS PEARS",
"UNKNOWN FALLBACK"))
Then, once the formula is written, just fill down.