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".
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.
