Check cell for a specific letter or set of letters

In a Google Spreadsheet, I want to use a formula that will output a certain text or number if a certain cell contains certain letters.

For example, if I put =IF(A1="Black";"YES";"NO") and A1 is equal to "Black" - it outputs "YES" correctly. However when I put =IF(A1="Bla";"YES";"NO") it outputs "NO". Is there any formula that I can use that I can say something like =IF(A1 CONTAINS "Bla";"YES";"NO") so it outputs "YES".

Solution 1:

You can use RegExMatch:

=IF(RegExMatch(A1;"Bla");"YES";"NO")

Solution 2:

Some options without REGEXMATCH, since you might want to be case insensitive and not want say blast or ablative to trigger a YES. Using comma as the delimiter, as in the OP, and for the moment ignoring the IF condition:

First very similar to @user1598086's answer:

=FIND("bla",A1)

Is case sensitive but returns #VALUE! rather than NO and a number rather than YES (both of which can however be changed to NO/YES respectively).

=SEARCH("bla",A1)  

Case insensitive, so treats Black and black equally. Returns as above.

The former (for the latter equivalent) to indicate whether bla present after the first three characters in A1:

=FIND("bla",A1,4)  

Returns a number for blazer, black but #VALUE! for blazer, blue.

To find Bla only when a complete word on its own (ie between spaces - not at the start or end of a 'sentence'):

=SEARCH(" Bla ",A1) 

Since the return in all cases above is either a number ("found", so YES preferred) or #VALUE! we can use ISERROR to test for #VALUE! within an IF formula, for instance taking the first example above:

 =if(iserror(FIND("bla",A1)),"NO","YES")  

Longer than the regexmatch but the components are easily adjustable.

Solution 3:

You can use the following formula,

=IF(ISTEXT(REGEXEXTRACT(A1; "Bla")); "Yes";"No")