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

.Dataset - Excel If Cell Contains Specific Text

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.

New Column Remarks - Excel If Cell Contains Specific Text

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","")      

IF Formula code to check cell contains specific text

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.

Result of IF Formula code to check cell contains specific text

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.

All cells result of IF Formula code to check cell contains specific text

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.

IF Formula code to check cell contains specific text case insensitive

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.

New columns for example - Excel If Cell Contains Specific Text

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.

Search String set - Excel If Cell Contains Specific Text

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

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

IF FIND Formula code to check cell contains specific text

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

Result of IF FIND Formula code to check cell contains specific text

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),"")

Updated IF FIND Formula code to check cell contains specific text

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.

Result of IF MID FIND Formula code to check cell contains specific text

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

Case Sensitive IF FIND Formula code to check cell

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

All Result of IF MID FIND Formula code to check cell contains specific text

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

Updated result for IF FIND Formula code to check cell contains specific text

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),"")

IF MID SEARCH Formula code to check cell contains specific text

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.

Result of IF MID SEARCH Formula code to check cell contains specific text

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.

All result of IF MID SEARCH Formula code to check cell contains specific text

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

Change in String Search for checking 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),"")

IF COUNTIF Formula code to check cell contains specific text

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

Result of IF COUNTIF Formula code to check cell contains specific text

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.

All result of IF SEARCH Formula code to check cell contains specific text

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

Change of string change of result - Excel If Cell Contains Specific Text

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.

Tags:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo