searching a sheet with a true and false function

[Aaron_excel]

Hello, I am trying to search a column to give me a true or false value in the column next to it. I do not know how to write the formula I need. I would like to search column B row 2 through 10. If the cell contains the word 'pencil' and excludes the words 'case', 'grip', 'sharpener', and 'mechanical' I would like the corresponding cell in column C to read TRUE. Is there a formula I could use for this? 

[#2 · Accepted solution]

Hello @Aaron_excel,

Refer here:

https://exceljet.net/formula/cell-contains-some-words-but-not-others

[#3]

Thank you so much! My google searches gave me everything but hard coding text. Saved my bacon!@PReagan 

[#4]

@Aaron_excel 

Glad to help!

[#5]

=AND(COUNT(SEARCH({"*pencil*"},B2))>0,COUNT(SEARCH({"*case*","*color*","*colored*","*grip*","*eraser*","*mechanical*","*sharpener*"},B2))=0)

The above formula is what I created giving me a true or false in the cell. Is there a way to count how many came back true? Also Can I search just for the cells that came back true? I am trying to navigate with thousands of rows. @PReagan 

[#6]

@Aaron_excel 

With the help of column C, the count of TRUE could simply be:

=COUNTIF(C2:C10,TRUE)

Without the help of column C, that could be:

 =COUNT(SEARCH({"*pencil*"},B2:B10))-COUNT(SEARCH({"*case*","*color*","*colored*","*grip*","*eraser*","*mechanical*","*sharpener*"},B2:B10))}

Note: I would recommend creating a table with included/excluded strings as the list of excluded could easily get quite lengthy.