Check cell for a specific letter or set of letters

Question

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

User · Answer

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

User · Answer

You can use RegExMatch    IF RegExMatch A1  Bla    YES   NO

User · Answer

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

User · Answer

You can use the following formula    IF ISTEXT REGEXEXTRACT A1   Bla      Yes   No

[google-sheets] Check cell for a specific letter or set of letters

Examples related to google-sheets