How to Find If Cell Contains Specific Text in Excel

In Excel, circumstances may demand checking whether a cell contains a specific text or not. Today we are going to show you how to check if a cell contains specific text. For this session, we are using Excel 2019, feel free to use your preferred version.

First things first, let’s get to know about the dataset which is the base of our examples

.

We have a table that contains several students’ information with their grades. Using this dataset, we will check if a cell contains specific text or not.

Note that, this is a basic dataset to keep things simple. In a real-life scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

If Cell Contains Specific Text

1. Cell Contains Specific Text Only

We can look through a cell that may (or may not) contain exactly only the text (string) we are looking for. There will be no additional strings with it.

For example, in our dataset, the Grade column has Passed or Failed in every cell. No extra words or strings are not there. So, we can check whether a cell within this column contains Passed or Failed.

Here, for example, we will check whether a cell contains “Passed” or not and then add a remark at the newly introduced Remarks column.

Hearing words like “check”, one of the first functions that come into your mind is IF. The IF function runs a logical test and returns a Binary value (TRUE or FALSE).

Let’s write the formula using IF to check whether the cell contains the specific text “Passed” or not.

Here, we have set a logical operation  D4=”Passed” that compares if the D4 contains “Passed” or not.

For if_true_value we have set “Promoted”, it will appear as a result once it found the cell contains the text. For the time being, no if_false_value is provided.

The D4 cell has the searching text, “Passed”, so the formula returned the if_true_value.

Now, write the formula for the rest of the cells. You can exercise the AuoFill feature as well.

You can see, our formula has returned the if_true_value, “Promoted”, perfectly for the cells that contain “Passed”.

This works in a case-insensitive way. If any of the cells contain “passed” instead of “Passed”, it will also work.

2. Cell Contains Specific Text (Partial Match)

Sometimes we may need to search for a specific text within a cell as a substring. In this section, we will see how to do that.

For example, we will search for a string (group name) within the cells of the ID column.

Here we have introduced a couple of columns that match the context of our example.

You can see for the Search String column we have merged the rows. Follow this merging rows article for the techniques.

I. Match through FIND Function (Case Sensitive)

In the earlier section, we have used IF for checking the text (using the logical expression). This function will be in use though we need other supporting functions.

The FIND function is a function that we are going to pair up with IF to check whether a cell contains the specific text at least as a substring.

For example, we have chosen a string “AB” that we will search within cells from the ID column.

Now, let’s write the formula for the B4 cell.

Here you can see the function ISNUMBER. ISNUMBER returns TRUE when a cell contains a number, and FALSE if not

We used this function because it checks whether the FIND function’s result is a number or not. It returns a boolean value.

When the ISNUMBER returns TRUE then the IF function will trigger the if_true_value (Found), otherwise the if_false_value (Not Found).

Here for the first cell, the ISNUMBER-FIND returned TRUE and the final output became “Found”.

Let’s extract the group name. For that, we will use the MID function. This function extracts characters from the middle of a given string.

First, we have checked whether the cell contains the specific text, then at theif_true_value field, we have set the MID function to fetch the value. FIND within MID provides the starting point and then 2 characters. This will fetch the two charactered group names.

We have found the group name when the cell contains the text.

Since FIND is case sensitive, it will not execute if_true_value for “ab”.

Write the code for the rest of the cells. You will find a group name that has been written exactly as the Search String.

Change the Search String value, you will find the updated result.

II. Match through SEARCH Function (Case Insensitive)

In the earlier section, we have observed an approach that is a case-sensitive one. In order to keep things flexible, we can adopt a case-insensitive approach. For that, the SEARCH function will be useful.

SEARCH returns the location of one text string inside another. It operates similar to the FIND function, but it is case-insensitive.

The formula is as follows

It seems similar that to the FIND section. The only change is we have replaced FIND with SEARCH. The rest of the formula is the same and operates exactly the same way.

We have found the group name when the cell contains the text.

Write the formula for the rest of the cells. You will find all the group names that have “AB” in any form.

If we write “ab” as the Search String still it will fetch these values.

III. Match using COUNTIF Function (Case Insensitive)

Another way of checking cell contains specific text as a substring is combining IF and COUNTIF. This approach will also be a case-insensitive one.

This COUNTIF function counts cells in a range that meets a single condition.

Now the formula will be the following one.

Here we have checked the logic using COUNTIF. Using COUNTIF it checks whether the count value is 1 or not.

If COUNTIF returns 1 then the if_true_value (MID portion to extract group name) will be returned. The MID portion is discussed in the earlier section.

For 0 from the COUNTIF portion, the formula will return if_false_value (empty cell, for the time being).

We have found the match and then the formula returned the group name when the cell contains the specific text.

Write the formula for the rest of the cells. You will find all the group names that have the Search String in any form.

Let’s change the Search String value, updated results will be in front of us.

For more COUNTIF partial match approaches visit this COUNTIF PARTIAL MATCH article. If you are interested in partial matching with if, this IF Partial Match article can be helpful for you.

Conclusion

That’s all for today. We have listed several approaches to check if a cell contains a specific text in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we might have missed here.