To check whether a cell contains 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 specific text inside a cell, and FALSE otherwise. This version is case-insensitive.
1 |
=ISNUMBER(SEARCH(substring,string)) |
If you want to use the case-sensitive version, you can use the following formula.
1 |
=ISNUMBER(FIND(substring,string)) |
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.
1 |
=COUNTIF(A1:C4,"six") |
- Count cells that contain the specified text.
1 |
=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:
1 |
=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 the remaining cells to get the following result.