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

2018/07/10
1
124
7/10/2018 9:30:28 AM

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.

2019/09/12

You can use the following formula,

``````=IF(ISTEXT(REGEXEXTRACT(A1; "Bla")); "Yes";"No")
``````
2018/07/10

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.

2015/05/28