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.

**Table of Contents**Expand

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

`=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***Promoted**”, and it will appear as a result once it finds the cell contains the text. - And for the time being, no
*if_false_value*

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

`=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***Promoted**”, it will appear as a result once it finds the cell containing the text. - And for the time being, no
*if_false_value*

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

`=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. Otherwise the*if_true_value*.*if_false_value* - Moreover, in the case of
term, we will extract the group name.*if_true_value* - 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. 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.

`=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. Otherwise the*if_true_value*.*if_false_value* - Moreover, in the case of
term we will extract the group name.*if_true_value* - 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. 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.

`=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(empty cell, for the time being).*if_false_value*

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

`=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. 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:

`=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. 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
```

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