We have a table that contains student information and their grades. We will check if a cell contains specific text.
Method 1 – Using the IF Function to Check If a Cell Contains an Exact Text in Excel
The Grade column has Passed or Failed in every cell. We will check whether a cell contains Passed and add a remark in a seocnd Remarks column.
- Use the following formula in E5 to check whether the cell D5 contains the specific text Passed.
=IF(D5="Passed","Promoted","")
Formula Breakdown
- We have set a logical operation D5=“Passed” that compares if the D5 contains “Passed” or not.
- For if_true_value ,we have set Promoted, which will appear as a result once it finds the cell contains the text.
- No if_false_value is provided.
- Hit Enter to get the first result.
- Repeat the formula for the rest of the cells. Alternatively, you can drag or double-click the Fill Handle icon to paste the used formula to the other cells of the column.
Our formula has returned the if_true_value Promoted for the cells that contain Passed. This check is not case-insesitive, so the formula will register text strings that don’t use lowercase letters.
Method 2 – Combining the IF and EXACT Functions to Check If a Cell Contains Specific Text
We will check whether a cell contains the exact text Passed and add a remark in the Remarks column.
- Use the formula given below in the E5 cell.
=IF(EXACT("Passed",D5),"Promoted","")- Press Enter.
Formula Breakdown
- We have set a logical operation EXACT(“Passed”,D5) that compares the text in the D5 cell with Passed.
- For the if_true_value, we have set Promoted, which will appear as a result if the formula finds a cell containing the text.
- No if_false_value is provided.
- Drag the Fill Handle icon down to AutoFill the corresponding data in the rest of the cells E6:E14.
Method 3 – Joining IF, ISNUMBER, FIND, and MID Functions to Search for a Specific Text (Case-Sensitive)
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. We chose the string “XG” that we will search within cells from the ID column. The lookup value is in E5 (a merged cell).
- Use the following formula in the F5 cell.
=IF(ISNUMBER(FIND($E$5,B5)),MID(B5,FIND($E$5,B5),2),"")- Press Enter.
Formula Breakdown
- 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, the IF function will trigger the if_true_value. Otherwise, it goes to if_false_value.
- In the case of if_true_value term, we will extract the group name.
- For extracting the group name, we will use the MID function. This function extracts characters from the middle of a given string. FIND within MID provides the starting point and then MID extracts 2 characters from that point. This is due to the lookup string being two characters long.
- Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F6:F14.
Method 4 – Combining IF, ISNUMBER, SEARCH, and MID Functions to Find a Specific Text (Case-Insensitive)
- Use the following formula in the F5 cell.
=IF(ISNUMBER(SEARCH($E$5,B5)),MID(B5,SEARCH($E$5,B5),2),"")- Press Enter.
Formula Breakdown
- ISNUMBER returns TRUE when a cell contains a number, and FALSE if not.
- 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.
- In the case of if_true_value term we will extract the group name.
- 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.
- Double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F6:F14.
Method 5 – Merging IF, COUNTIF, SEARCH, and MID Functions to Find a Particular Text (Case-Insensitive)
- Use the following formula in F5.
=IF(COUNTIF(B5,"*"&$E$5&"*"),MID(B5,SEARCH($E$5,B5),2),"")- Hit Enter.
Formula Breakdown
- W have checked the logic using COUNTIF. Using COUNTIF it checks whether the count value is 1 or not.
- If COUNTIF returns 1, 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).
- AutoFill the formula through the column.
For more COUNTIF partial match approaches, visit this COUNTIF Partial Match article.
Method 6 – Joining ISNUMBER and SEARCH Functions to Check If a Cell Contains Specific Text in Excel (Case-Insensitive)
- Use the formula given below in the F5 cell.
=ISNUMBER(SEARCH(E5,B5:B14))
- Hit Enter and AutoFill.
Method 7 – Joining SUMPRODUCT and COUNTIF Functions to Search for a Specific Text (Case-Insensitive)
- Use the following formula in F5.
=SUMPRODUCT(COUNTIF(B5,"*"&$E$5&"*"))>0- Hit Enter.
Formula Breakdown
- The range is B5:B10.
- Criterion is “*”&D5&”*”.
- The COUNTIF function counts the number of matched cells.
- The SUMPRODUCT function takes the number returned by the COUNTIF function and gets its sum.
- Apply the same formula to the rest of the cells via AutoFill.
Method 8 – Applying Excel VBA to Check If a Cell Contains Specific Text
Steps:
- Go to the Developer tab and select Visual Basic.
- From the Insert tab, select Module.
- Insert 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.
- InStr will search for the string Passed.
- We used a MsgBox to show the result.
- Save the code by pressing Ctrl + S. Use the type extension .xlsm.
- Go back to the Excel worksheet.
- Select any cell from the Grade column.
- From the Developer tab, select Macros.
- Select the Macro (If_Contains_Specified_Text) and click on Run.
- You will find a message that displays the result.
- We have selected another cell D7 and got the following message.
Practice Section
Use the practice dataset in the download file to test these methods yourself.
Download the Practice Workbook
<< Go Back to Find in String | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
