To check whether a cell contains the specific text, you can use the SEARCH function. This function will return the position of the first match and the **#VALUE!** error otherwise.

Because we don’t want to have errors in our formula, we are going to use the **SEARCH** function with the ISNUMBER function. **ISNUMBER** returns **TRUE** if there is a number and **FALSE** if there is anything else.

The following formula will return **TRUE** if there is the specific text inside a cell, and **FALSE** otherwise. This version is case-insensitive.

=ISNUMBER(SEARCH(substring,string))

If you want to use the case-sensitive version, you can use the following formula.

=ISNUMBER(FIND(substring,string))

Contents

## If cells contain the specific text then count

Here, we are going to count all cells that contain the specified text. There are two ways to count these cells. In this case, we are going to use the following formulas:

- Count cells that contain the entire text and nothing more.

=COUNTIF(A1:C4,"six")

- Count cells that contain the specified text.

=COUNTIF(A1:C4,"*six*")

In the first example (**C6**), Excel will return 1 because only one cell (**B2**) meets the criteria.

The second example will return three because cells **B2**, **C2**, and **C3** meet the criteria.

## If cells contain the specific text then return value in another cell

We can simply modify this example to copy the cell to a different column if the cell contains the specific text.

Copy all the data from **A1** to **C4** to column **A**.

Inside cell **B1**, enter the following formula:

=IF(COUNTIF(A1,"*six*")>0,A1,"-")

This formula checks whether there is “six” inside cell **A1**. If **TRUE**, it copies cell **A1** to **B1**. If **FALSE** it inserts a dash. Autofill remaining cells to get the following result.