To check whether a cell contains a specific text string, you can use Search Function or Find Function, which will tell you the location of the string in the cell. The difference between the two Functions is that Search Function is not case sensitive, while Find Function is case sensitive.
For example, if you check "How to" only, you need to use the Find Function, and if you check "How to" or "how to", you should use the Search Function.
Formula:
Change the word of "Check" in the formula below with your text string, and change A2 to the cell you need to check, then copy the formula down to all cells.
=IF(ISNUMBER(SEARCH("Check",A2))=TRUE,A2,"") not case sensitive;
=IF(ISNUMBER(FIND("Check",A1))=TRUE,A1,"") case sensitive.
Explanations:
Step 1: Look for the location of the text string "son";
| Formula | =SEARCH("son",A4) |
|---|---|
| Result | 13 |
Step 2: Search (or Find) function returns a number, combine with ISNUMBER function and IF function to return the text string in the cell.
| Formula | =ISNUMBER(SEARCH("son",A2)) |
|---|---|
| Result | True (or false if not contains the text) |
Step 3: Use IF function to return the cell value when it is a number, and leave blank when not.
| Formula | =IF(ISNUMBER(SEARCH("son",A4))=TRUE,A4,"") |
|---|---|
| Result | Elijah Anderson |
Please replace Search Function with Find Function if you search for a text string with case sensitive.