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***Promoted**, which will appear as a result once it finds the cell contains the text. - No
*if_false_value*

- 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,***Promoted**, which will appear as a result if the formula finds a cell containing the text. - No
*if_false_value*

- 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. Otherwise, it goes to*if_true_value*.*if_false_value* - In the case of
term, we will extract the group name.*if_true_value* - 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. Otherwise the*if_true_value*.*if_false_value* - In the case of
term we will extract the group name.*if_true_value* - 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(empty cell, for the time being).*if_false_value*

- 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**() and click on*If_Contains_Specified_Text***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**