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 Microsoft 365 version.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
8 Simple Methods to Check If Cell Contains Specific Text in Excel
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. Use IF Function to Inspect If Cell Contains an Exact Text
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.
=IF(D5="Passed","Promoted","")
Formula Breakdown
- Here, we have set a logical operation D5=“Passed” that compares if the D5 contains “Passed” or not.
- Then, for if_true_value we have set “Promoted”, it will appear as a result once it found the cell contains the text.
- And for the time being, no if_false_value is provided.
- 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 respectively 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. Combine Excel IF & EXACT Functions to Check If Cell Contains Specific Text
Here, you can use another method by combining the IF and EXACT function 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.
=IF(EXACT("Passed",D5),"Promoted","")
- Thirdly, press ENTER.
Formula Breakdown
- Here, we have set a logical operation EXACT(“Passed”,D5) that compares if the text in the D5 cell is the same as “Passed” or not.
- Then, for if_true_value we have set “Promoted”, it will appear as a result once it finds the cell containing the text.
- And for the time being, no if_false_value is provided.
- 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.
Read more: Excel Search for Text in Range
3. Integrate IF, ISNUMBER, FIND & MID Functions (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. Follow this merging rows article for the techniques.
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.
=IF(ISNUMBER(FIND($E$5,B5)),MID(B5,FIND($E$5,B5),2),"")
- Then, press ENTER.
Formula Breakdown
- Here you can see the function ISNUMBER. ISNUMBER returns TRUE when a cell contains a number, and FALSE if not.
- Then, 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. Otherwise the if_false_value.
- Moreover, in the case of if_true_value term we will extract the group name.
- Now, for extracting the group name, we will use the MID function. This function extracts characters from the middle of a given string. Basically, FIND within MID provides the starting point and then 2 characters. This will fetch the two character group names.
- 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. Consolidate IF, ISNUMBER, SEARCH & MID Functions (Case Insensitive)
In the earlier section, we observed an approach that is case-sensitive one. 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 similar to the FIND function, but it is case-insensitive.
- So, write down the formula in the F5 cell.
=IF(ISNUMBER(SEARCH($E$5,B5)),MID(B5,SEARCH($E$5,B5),2),"")
- Consequently, press ENTER.
Formula Breakdown
- Here you can see the function ISNUMBER. ISNUMBER returns TRUE when a cell contains a number, and FALSE if not.
- Then, we used this function because it checks whether the SEARCH 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. Otherwise the if_false_value.
- Moreover, in the case of if_true_value term we will extract the group name.
- Now, for extracting the group name, we will use the MID function. This function extracts characters from the middle of a given string. Basically, SEARCH within MID provides the starting point and then 2 characters. This will fetch the two character group names.
- 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. Merge IF, COUNTIF, SEARCH & MID Functions (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.
=IF(COUNTIF(B5,"*"&$E$5&"*"),MID(B5,SEARCH($E$5,B5),2),"")
- Then, press ENTER.
Formula Breakdown
- 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. Furthermore, 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).
- 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. Unite ISNUMBER & SEARCH Functions (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.
=ISNUMBER(SEARCH(E5,B5:B14))
Formula Breakdown
- Here, you can see the function ISNUMBER. Basically, ISNUMBER returns TRUE when a cell contains a number, and FALSE if not.
- Actually, we used this function because it checks whether the SEARCH function’s result is a number or not. It returns a Boolean value.
- Lastly, press ENTER and you will see all the statuses.
7. Join SUMPRODUCT & COUNTIF Functions (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:
=SUMPRODUCT(COUNTIF(B5,"*"&$E$5&"*"))>0
- Hence, press ENTER to get the result.
Formula Breakdown
- The range is B5:B10.
- Criterion is “*”&D5&”*”.
- The COUNTIF function counts the number of matched cells.
- In addition, the SUMPRODUCT function takes the number returned by the COUNTIF function and gets its sum.
- Lastly, apply the same formula to the rest of the cells. The result is accurate concerning the input.
8. Apply 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
Code Breakdown
- Here, we have created a Sub Procedure named If_Contains_Specified_Text.
- Next, InStr will search for the string Passed.
- After that, we used a MsgBox to show the result.
- 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.
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.