If cell contains X, Y or Z then write ...
Solved
Brindeau
-
Brindeau -
Brindeau -
Hello,
I am an intern student and I just wasted my afternoon trying to solve a formula problem on a large Excel document.
Here it is:
https://drive.google.com/file/d/0B-Fi99za9paUU0NfUk9iaDZ4TFk/edit?usp=sharing
Here is my problem:
I want to display in a column the name of a category based on whether the cell in my "abstract" column contains a specific keyword.
Using just one word, I used the formula:
=IF(IFERROR(SEARCH("*diabet*",$B3),0),"Diabetes prevention"," ")
But I would like "diabetes prevention" to appear in the last column if ANY OF the words diabet*, sugar*, insulin* appear in the "abstract" column.
Bonus question: if you also know how to display all my categories in the same column. That is, if we have the words diabet*, sugar,... in column 2, then we write diabetes prevention; but also if we have the words fat, LDL, cholesterol,... we write obesity prevention, etc... with four or five categories of two or three keywords each.
I bless your formula! ^^
Thank you so much for your help, I need to analyze this document by tomorrow and I'm really in a... well, you get the point. Thanks in advance :)
I am an intern student and I just wasted my afternoon trying to solve a formula problem on a large Excel document.
Here it is:
https://drive.google.com/file/d/0B-Fi99za9paUU0NfUk9iaDZ4TFk/edit?usp=sharing
Here is my problem:
I want to display in a column the name of a category based on whether the cell in my "abstract" column contains a specific keyword.
Using just one word, I used the formula:
=IF(IFERROR(SEARCH("*diabet*",$B3),0),"Diabetes prevention"," ")
But I would like "diabetes prevention" to appear in the last column if ANY OF the words diabet*, sugar*, insulin* appear in the "abstract" column.
Bonus question: if you also know how to display all my categories in the same column. That is, if we have the words diabet*, sugar,... in column 2, then we write diabetes prevention; but also if we have the words fat, LDL, cholesterol,... we write obesity prevention, etc... with four or five categories of two or three keywords each.
I bless your formula! ^^
Thank you so much for your help, I need to analyze this document by tomorrow and I'm really in a... well, you get the point. Thanks in advance :)
9 réponses
Hello
indeed, we cannot access the file
As for the formula, I can give you that in English version (subject to confirmation, as I'm not too familiar with it in English)
=IF(OR(COUNTIF(B3,"*diabet*"),COUNTIF(B3,"*issulin*"),COUNTIF(B3,"*sugar*")), "Diabet prevention", "")
As for stacking everything in the same column, it should be enough, if your Excel allows it, to repeat the IFs for each set with the desired result
=IF(OR(---,---,---), "result 1", IF(OR(---,---,---), "result 2", etc... ,""))))
Best regards
To err is human, to persist is devilish
indeed, we cannot access the file
As for the formula, I can give you that in English version (subject to confirmation, as I'm not too familiar with it in English)
=IF(OR(COUNTIF(B3,"*diabet*"),COUNTIF(B3,"*issulin*"),COUNTIF(B3,"*sugar*")), "Diabet prevention", "")
As for stacking everything in the same column, it should be enough, if your Excel allows it, to repeat the IFs for each set with the desired result
=IF(OR(---,---,---), "result 1", IF(OR(---,---,---), "result 2", etc... ,""))))
Best regards
To err is human, to persist is devilish
