How to Check If Cell Contains Specific Text in Excel (8 Methods)

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.

Furthermore, for conducting the session, I will use the Microsoft 365 version.

Here, we will describe 8 effective methods to check if a cell contains specific text in Excel.

First, let’s get to know about the dataset which is the base of our examples. Basically, we have a table that contains several students’ information with their grades. Now, using this dataset, we will check if a cell contains specific text or not.

Actually, this is a basic dataset to keep things simple. Though you may encounter a much larger and more complex dataset in a real-life scenario.

1. Using IF Function to Check If Cell Contains an Exact Text in Excel

Here, we can look through a cell that may (or may not) contain exactly only the text (string) we are looking for. Actually, we’re talking about whether the cell contains the exact text or not.

For example, in our dataset, the Grade column has Passed or Failed in every cell. Basically, no extra words or strings are 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 in the newly introduced Remarks column.

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

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

• After pressing ENTER you will see the result.

As the D5 cell has the searching text, “Passed”, the formula returned the if_true_value.

• Now, write the formula for the rest of the cells. Here, you can drag the Fill Handle icon to paste the used formula to the other cells of the column.

As a result, you can see, our formula has returned the if_true_value, “Promoted”, perfectly for the cells that contain “Passed”.

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

2. Combining Excel IF & EXACT Functions to Check If Cell Contains Specific Text

Here, you can use another method by combining the IF and EXACT functions to check if the cell contains the specified or exact text or not.

Now, like the previous one, we will check whether a cell contains “Passed” or not and then add a remark in the Remarks column.

• Firstly, you must select cell E5 where you want to keep the remarks.
• Secondly, you should use the formula given below in the E5 cell.

• Thirdly, press ENTER.

• Then, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E14.
• Lastly, you will get all the remarks.

3. Joining IF, ISNUMBER, FIND & MID Functions to Search for a Specific Text (Case Sensitive)

Sometimes we may need to search for a specific text within a cell as a substring. From 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.

Furthermore, you can see for the Search String column we have merged the rows.

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

Actually, 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 “XG” that we will search within cells from the ID column.

• Now, let’s write the formula in the F5 cell.

• Then, press ENTER.

• Then, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F6:F14.
• Lastly, you will get who is from group XG.

In addition, since FIND is case sensitive, it will not execute if_true_value for “xg”.

4. Combining IF, ISNUMBER, SEARCH & MID Functions to Find a Specific Text (Case Insensitive)

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

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

• So, write down the formula in the F5 cell.

• Consequently, press ENTER.

• After that, double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F6:F14.
• Lastly, you will get those from group AB.

5. Merging IF, COUNTIF, SEARCH & MID Functions to Find a Particular Text (Case Insensitive)

Another way of checking if a cell contains specific text as a substring is by combining IF, COUNTIF, SEARCH, and MID functions. Actually, this approach will also be a case-insensitive one. Here, this COUNTIF function counts cells in a range that meets a single condition.

• Now, the formula will be the following one.

• Then, press ENTER.

• Similarly, write the formula for the rest of the cells.

As a result, you will find all the group names that have the Search String in any form.

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.

6. Joining ISNUMBER & SEARCH Functions to Check If Cell Specific Text in Excel (Case Insensitive)

Here, we will use a combination of ISNUMBER and SEARCH functions to check if a cell contains specific text in Excel. So, follow the steps given below.

• Firstly, you must select a new cell F5 where you want to keep the status.
• Secondly, you should use the formula given below in the F5 cell.

• Lastly, press ENTER and you will see all the statuses.

7. Joining SUMPRODUCT & COUNTIF Functions to Search for a Specific Text (Case Insensitive)

The SUMPRODUCT and COUNTIF functions also help you to find specific text in a cell. Follow these steps to learn. The SUMPRODUCT on the other hand the COUNTIF will help us to count values if that specific condition is met.

• First, apply the SUMPRODUCT function in cell F5. Here, we nested the COUNTIF function within the SUMPRODUCT. The final formula is:

• Hence, press ENTER to get the result.

• Lastly, apply the same formula to the rest of the cells. The result is accurate concerning the input.

8. Applying Excel VBA to Check If Cell Contains Specific Text

Here, you can employ the VBA code to check if a cell contains specific text. The steps are given below.

Steps:

• Firstly, you have to choose the Developer tab >> then select Visual Basic.

• Now, from the Insert tab >> you have to select Module.

• At this time, you need to write down the following Code in the Module.

Sub If_Contains_Specified_Text()
    If InStr(ActiveCell.Value, "Passed") > 0 Then
        MsgBox "This cell contains that specified text."
    Else
        MsgBox "This cell doesn't contain that text."
    End If
End Sub

• Now, you have to save the code by pressing CTRL+S and the code extension will be .xlsm.
• Then, you need to go to the Excel worksheet.
• Now, select any cell from the Grade column.
• Then, from the Developer tab >> select Macros.

• At this time, select Macro (If_Contains_Specified_Text) and click on Run.

• Finally, you will find the following message from Microsoft Excel.

• Here, to make you more understandable, we have selected another cell D7, and got the following message.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

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

Conclusion

That’s all for today. Here, 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.

<< Go Back to Find in String | String Manipulation | Learn Excel