F and ISNUMBER functions

[Helsinkiniano]

Hi,

my culumn CG have answers to the question: "Have you experienced any human-wildlife conflict?", and the possible answers are "no", "yes: elephants", "yes, mices", "yes: elephants; mices" or other combinations with other animals.

I want to create new columns to split this responses in separate variables, one for each animal. So, for example, a new column for elephants with "yes" or "no" as possible answers, one for mices, ect.

I tried this formula: =IF(ISNUMBER(SEARCH("elephants", CG3)), "Yes", "No") but I get an error message.

Thank you!

[#2]

@Helsinkiniano 

=LET(

    animals, {"elephants", "mices"}, IF(ISNUMBER(SEARCH(animals, CG3)), "Yes", "No)

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@Helsinkiniano 

=IF(ISNUMBER(SEARCH(CH$2, $CG3)), "Yes", "No")

This formula dynamically references the type of animal in the column header.

[#4]

One mouse => two mice.
Please do not add an s at the end of mice: it is plural already.

[#5]

@Domisa360 

Well...