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.

**Table of Contents**hide

## 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.

`=IF(D4="Passed","Promoted","") `

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.

`=IF(ISNUMBER(FIND($E$4,B4)),”Found”,"Not Found")`

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.

` =IF(ISNUMBER(FIND($E$4,B4)),MID(B4,FIND($E$4,B4),2),"")`

First, we have checked whether the cell contains the specific text, then at the*if_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

`=IF(ISNUMBER(SEARCH($E$4,B4)),MID(B4,SEARCH($E$4,B4),2),"")`

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.

`=IF(COUNTIF(B4,"*"&$E$4&"*"),MID(B4,SEARCH($E$4,B4),2),"")`

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.