Search text formula.

[molosser]

Hi,

I have the following example:

Cell A1 contains text; 180CEST
Cell A2 contains text; 180DEST
Cell A3 contains text; 180EEST
Cell A4 contains text; 180AEST
Cell A5 contains text; 180FEST

What's the best formula combination so that I get the result "YES" if A1:A5 contains the word "FEST" and "NO" if the the word isn't there? Case must be insensitive so that any combination of capitals or small letters doesn't matter. I know this must involves IF and SEARCH functions but I just can't seems to get it work.

Regards.

[AlKey]

Try this formula

=IF(ISNUMBER(SEARCH("FEST",A1)),"Yes","No")

	A	B	C
1	180DEST		No
2	180EEST		No
3	180AEST		No
4	180FEST		Yes

[cbatrody]

=if(countif(a1:a4,"*"&"fest"&"*")>=1,"yes","no")

[ben_hensel]

I got it with an array formula (confirm with ctrl+shift+enter, not just enter key):

Please Login or Register  to view this content.

That will add up all the results of SEARCH while treating errors as zero, and use that as the input to the IF test.

[AlKey]

@cbatrody

It could be even smaller

=IF(COUNTIF(A1:A4,"*FEST*"),"Yes","No")

[Tony Valko]

If the string FEST is always the 4 right-most characters:

=IF(COUNTIF(A1:A5,"*FEST"),"Yes","No")

[cbatrody]

Another way,

=IF(ISTEXT(VLOOKUP("*FEST*",A1:A5,1,0)),"YES","NO")

[oeldere]

if the word fest is always on the end of the text you could use also this solution.

b1=if(right(a1,4)="fest","yes","no")

and drag down.

c1= if(countif(b1:b5,"yes")>=1,"yes";"no")

[molosser]

Sorry for the very late reply guys, appreciate all the help very much.
Turns out, using the COUNTIF function is the simplest way for me.

[Tony Valko]

Good deal. Thanks for the feedback!