Check cell for a specific letter or set of letters

Google Sheets

Google Sheets Problem Overview

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

Google Sheets Solutions

Solution 1 - Google Sheets

You can use RegExMatch:


Solution 2 - Google Sheets

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:


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 and 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 #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:


Longer than the regexmatch but the components are easily adjustable.

Solution 3 - Google Sheets

You can use the following formula,

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

Solution 4 - Google Sheets

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.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionuser1799724View Question on Stackoverflow
Solution 1 - Google SheetsflyingjamusView Answer on Stackoverflow
Solution 2 - Google SheetspnutsView Answer on Stackoverflow
Solution 3 - Google SheetsimbondView Answer on Stackoverflow
Solution 4 - Google SheetsTyler Depies-BobrowitzView Answer on Stackoverflow