formula to check if the first 25 charatures of a cell contain "Yes", "No", "Maybe"

[tonywatsonhelp]

Hi Everyone,
hope someone can help with this really stuck,
I need a formula that can tell me if cell "I10" contains either the word "Yes","No","Maybe" or none.

now the problem i'm having is this,
it can't be case sensitive so no, No,NO,nO all = "No"
and it needs to be just that word, so "Not Sure" for example is triggering as "No" the way i tried to do it,
But we can't just do "no" with a space in front or behind because it could be the first or last word in the cell???

So how can i have a formula that will tell me which word it finds if any?

so the results should be
"Yes"
"No"
"Maybe"
"Nothing found"


I'm really stuck and don't want to have to work the weekend so any help anyone can give would be great
Thanks

Tony

 

[DanteAmor]

Thanks for posting on the forum.
Try the following.

Order from least important to most important if the cell has more than one answer.

Dante Amor
	A								I	J
1
9
10									contains yes	Yes
11									contains No	No
12									No Maybe contains yes and no	Yes
13									maybe	Maybe
14									some NO word maybe	No
15									nothing	Nothing found
Hoja5

Cell Formulas
Range	Formula
J10:J15	J10	=IFERROR(LOOKUP(2,1/(SEARCH({" maybe ";" no ";" yes "}," " &I10 & " ")),{"Maybe";"No";"Yes"}),"Nothing found")

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

----- --

 

[tonywatsonhelp]

This is brilliant,
Thank you so much Dante
Tony

 

[DanteAmor]

I missed the following:

formula to check if the first 25 charatures of a cell contain "Yes", "No", "Maybe"

It was necessary to review the first 25 characters in the formula, so it would be like this:

=IFERROR(LOOKUP(2,1/(SEARCH({" maybe ";" no ";" yes "}," " &LEFT(I10,25) & " ")),{"Maybe";"No";"Yes"}),"Nothing found")

 

[tonywatsonhelp]

Thanks Dante,
I had worked that part out, but thanks for confirming i did it correctly
Tony