[google-sheets] 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".

This question is related to google-sheets

The answer is


You can use the following formula,

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

Just use = IF(A1="Bla*","YES","NO"). When you insert the asterisk, it acts as a wild card for any amount of characters after the specified text.


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.


You can use RegExMatch:

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