Check cell for a specific letter or set of letters

Question

In a Google spreadsheet, I want to use a formula that displays a specific text or number if a specific cell contains certain letters.

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

Answer 1

You can use RegExMatch :

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

Answer 2

Some parameters without REGEXMATCH , since you might want to be insenstive and don't want to say blast or ablative to call YES . Using a comma as a separator, as in the OP, and currently ignoring the IF condition:

At first very similar to @ user1598086 answer:

 =FIND("bla",A1) 

Case sensitive, but returns #VALUE! not NO and the number, not YES (both of them, however, can be changed to NO / YES, respectively).

 =SEARCH("bla",A1) 

Case insensitive, therefore, treats Black and Black equally. Returned as above.

The first (for the last equivalent) indicates 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 .

Find bla only when the full word itself (i.e. between spaces - not at the beginning or at the end of a sentence):

 =SEARCH(" Bla ",A1) 

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

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

Longer than REGEXMATCH , but components are easily customizable.

Answer 3

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