First time here? Check out the FAQ!

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.

2 | No.2 Revision |

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.

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.