## Subscribe for Weekly Tutorials

### BONUS: subscribe now to download our Top Tutorials Ebook!

# Determine if Cells Contain a Specific Value in Excel

Find if a cell or range of cells contains a specific value in Excel. This method can be used on individual cells when you want to "mark" them or to find out how many cells in a range contain that specific value.

With this method, it doesn't matter where the text, number, or value is located within the cell.

### Sections:

Find if a Value is Contained in a Specific Cell

Find if a Value is in a Range of Cells

Find a Value that is Separate from Other Text in a Cell

## Find if a Value is Contained in a Specific Cell

Let's find out if the word **red** is in cell A1.

- Go to an empty cell and type
**=COUNTIF(** - Select cell A1, the cell with the text, and then type a comma so we can move to the next argument in Step 3.
- Type
**"*red*"**Notice the symbol*****around the text. This is what tells the function to look for the word**red**anywhere inside the cell. You must put this symbol at the start and end of the value for which you are searching.

Also, note that everything is encapsulated inside of double quotes**""**. You must surround everything, including the*****symbol with double quotation marks, as in the example. - Type a closing parenthesis
**)**and then hit Enter.

You see 1 in the cell because the COUNTIF function counts how many times it found a value in a cell and it found the value**red**once in cell A1. If it did not find the value it would return a zero**0**.

We use this technique to determine if the value is in the cell. In our example here it doesn't matter how many times the value is in the cell, only that it is there. - That's it!

You can stop here and use this output any way you like, or you can add a more visual representation of this data by surrounding the COUNTIF function with an IF statement and doing something like this:

The output will be like this:

Here is the final code with the IF statement included:

```
```=IF(COUNTIF(A1,"*red*")>0,"Value Found", "Value Not Found")

Here is the code without the IF statement:

```
```= COUNTIF(A1,"*red*")

## Find if a Value is in a Range of Cells

This is almost exactly the same as the previous example except that we want to look through a range of cells to determine if the value is located there.

This method will also count how many times the desired value appears within the range.

To do this, follow the steps from the previous example, but, for Step 2, select a range of cells instead of just a single cell.

It will look like this:

With a result like this:

This means that the text **red** was found twice in the range of cells.

The COUNTIF function won't tell you the location of the cells with the text; it will only tell you that the text is present in the range and how many times it appears.

For a more detailed explanation of how this works, look to the previous example (the first example) in this tutorial.

## Find a Value that is Separate from Other Text in a Cell

This tutorial assumes that you are looking for a distinct value within a cell or range of cells and the above methods work well 95% of the time. However, those methods will also return values that are part of other values.

So, if I want to check if the word **red** appears in a cell using the above method but there are some cells where **red** is a part of a larger word, Excel will still count that as having found the word **red**, which is not what we want.

If your data might be setup like this, then we need to specify that we want to find only the whole word **red**. Since words are surrounded by spaces, we alter the initial formula to be like this:

```
```=COUNTIF(A6,"* red *")

Notice the spaces between the ***** symbols and the value **red**. This means that the word **red** will only be found/counted if it has a space to the left and right of it within the cell, which makes it a word instead of a part of a word.

In this example, it won't find **red** in cell A6:

The original formula would have counted the **red **in **Red**mond as 1.

In my data spaces worked fine, but you may need to use different characters to surround your text depending on that for which you are searching.

## Notes

There are other ways to do this, including using the FIND function, but I have found that the COUNTIF function works well, is a bit more versatile, and is easier for most people to understand and use.

This tutorial shows you a neat little way to use a counting function to determine if a cell contains a specific value or not. I hope this will help you to start thinking of creative ways to solve your Excel issues.

This method is not case sensitive.

Make sure to download the attached spreadsheet so you can work with these examples in Excel.

## Question? Ask it in our Excel Forum

#### Professional Input Forms in Excel

- Skill Level: All Levels
- |
- 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

#### Send Emails from Excel

- Skill Level: All Levels
- |
- 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Tutorial: Quickly find all rows in Excel that contain a certain value and then delete those rows. ...

Macro: This free Excel macro formats selected cells in the Scientific number format in Excel. Thi...

Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....

Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...

Tutorial: How to count cells that match text at the start or the end of a string in Excel. If you w...

Tutorial: In this tutorial I am going to cover how to highlight rows that meet a certain condition. ...