Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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 test 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.

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 test 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.