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".
You can use
Read more... Read less...
Some options without
REGEXMATCH, since you might want to be case insensitive and not want say
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:
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).
Case insensitive, so treats
black equally. Returns as above.
The former (for the latter equivalent) to indicate whether
bla present after the first three characters in A1:
Returns a number for
blazer, black but
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:
Longer than the
regexmatch but the components are easily adjustable.
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.