CODEWITHSUNDEEP
excelopenoffice.orgopenoffice-calc
r1987
r1987

Reputation: 537

Search for specific words in cells, add true or false if even one is present

So I've googled the internet, but can't seem to find a proper function.

I have cells containing some Categories and in each cell, they're different.

Example:

  1. Art, History
  2. Architecture, Design, Art, Magic
  3. Photography, Design

What I'm after is this, that if a cell contains even one word from a specific list, then it would add a true or false next to another cell.

I know there's a function to find a specific word from a cell and then true or false it:

=IF(ISNUMBER(SEARCH("Art";K3));"true";"false")

And I also found something that I need, but this doesn't seem to be working in openOffice:

=IF(COUNT(SEARCH({"Architecture","Magazines","Movie","Music","Photography","Theory"},K2)),"true","false")

How can this be done?

Upvotes: 0

Views: 1493

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

Your second formula will work also in Excel only as array formula. So even in openoffice an array formula is needed.

The formula is:

{=IF(SUM(NOT(ISERROR(SEARCH({"Architecture","Magazines","Movie","Music","Photography","Theory"},K2))))>0,"true","false")}

To enter an array formula in openoffice, enter the formula first and click fx then. In the Function Wizard dialog check the [x] Array checkbox. enter image description here

If you fill array formulas downwards with the cell handle, then you will create a range array. This is not the goal in this case. Thats why do not fill with the cell handle. Copy the formula and paste it into the other cells.

enter image description here

Upvotes: 2

Related Questions