Check If Cell Contains Partial Text in Excel (5 Ways)

You may need to look for a specific word throughout your dataset to extract some vital information. To serve the purpose all you need to do is check every single cell throughout your worksheet whether any of them contains your intended word in it. In order to help you guys in this regard, we’ve come up with 5 ways in this blog post that you can use to check if any cell contains partial text in Excel with ease.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


5 Ways to Check If Cell Contains Partial Text in Excel

In this article, we will be using a sample product price list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:

Dataset for 5 Ways to Check If Cell Contains Partial Text in Excel

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Check If Partial Text Contains in the Beginning

If you are looking for a partial match at the beginning of your texts then you can follow the steps below:

❶ Select cell E5 ▶ to store the formula result.

❷ Type the formula:

=IF(COUNTIF(B5,"MTT*"),"Yes","No")
within the cell.

❸ Press the ENTER button.

If and countif function

❹ Now drag the Fill Handle icon to the end of the Partial Text column.

Fill Handle icon

When you are done with all the steps above, you will see the formula result like the picture below:

  Formula Breakdown

  • COUNTIF(B5,”MTT*”) ▶ returns 1 if MTT exists at the beginning of the text otherwise returns 0.
  • =IF(COUNTIF(B5,”MTT*”),”Yes”,”No”) ▶ returns Yes if MTT exists at the beginning of the text otherwise returns No.

Read more: Excel If Cell Contains Text Then Return Value


2. Explore If Partial Text Contains in the End

You can follow the steps below if you are looking forward to exploring partial text that exists at the end of the text.

❶ Select cell E5 ▶ to store the formula result.

❷ Type the formula:

=IF(COUNTIF(B5,"*NPP"),"Yes","No")
within the cell.

❸ Press the ENTER button.

If and countif function to Explore If Partial Text Contains in the End

❹ Now drag the Fill Handle icon to the end of the Partial Text column.

When you are done with all the steps above, you will see the formula result like the picture below:

  Formula Breakdown

  • COUNTIF(B5,”*NPP”) ▶ returns 1 if NPP exists at the end of the text otherwise returns 0.
  • =IF(COUNTIF(B5,”*NPP”),”Yes”,”No”) ▶ returns Yes if NPP exists at the end of the text otherwise returns No.

3. Check If Partial Text Contains at any Position

If you want to run a blind search throughout the dataset i.e. to look for a partial match at any position then you can go through the following steps:

❶ Select cell E5 ▶ to store the formula result.

❷ Type the formula:

=IF(COUNTIF(B5,"*NQ*"),"Yes","No")
within the cell.

❸ Press the ENTER button.

Check If Partial Text Contains at any Position Using IF and COUNIF Function

❹ Now drag the Fill Handle icon to the end of the Partial Text column.

When you are done with all the steps above, you will see the formula result like the picture below:

  Formula Breakdown

  • COUNTIF(B5,”*NQ*”) ▶ returns 1 if NQ exists at any position of the text otherwise returns 0.
  • =IF(COUNTIF(B5,”*NQ*”),”Yes”,”No”) ▶ returns Yes if NQ exists at any position of the text otherwise returns No.

Similar Readings:


4. Examine If Partial Text with Specific Character Contains at the Beginning

Now we will mark all the cells that contain the partial text, 1VX40NQ followed by any one character. Now follow the steps below to see how to do it.

❶ Select cell E5 ▶ to store the formula result.

❷ Type the formula:

=IF(COUNTIF(B5,"?1VX40NQ"),"Yes","No")
within the cell.

❸ Press the ENTER button.

If and Counif function with wildcards

❹ Now drag the Fill Handle icon to the end of the Partial Text column.

When you are done with all the steps above, you will see the formula result like the picture below:

  Formula Breakdown

  • COUNTIF(B5,”?1VX40NQ”) ▶ returns 1 if 1VX40NQ exists followed by any single character; otherwise returns 0.
  • =IF(COUNTIF(B5,”?1VX40NQ”),”Yes”,”No”) ▶ returns Yes if 1VX40NQ exists followed by any single character; otherwise returns No.

5. Look Over If Partial Text with Specific Character Contains at the Beginning

Now let’s look for all the cells having the partial text OP666 and ending with any three characters. To see the procedure follow the steps below:

❶ Select cell E5 ▶ to store the formula result.

❷ Type the formula:

=IF(COUNTIF(B5,"OP666???"),"Yes","No")
within the cell.

❸ Press the ENTER button.

Look Over If Partial Text with Specific Character Contains at the Beginning

❹ Now drag the Fill Handle icon to the end of the Partial Text column.

When you are done with all the steps above, you will see the formula result like the picture below:

  Formula Breakdown

  • COUNTIF(B5,”OP666???”) ▶ returns 1 if OP666 is found throughout the texts and ends with any three characters; otherwise returns 0.
  • =IF(COUNTIF(B5,”OP666???”),”Yes”,”No”) ▶ returns Yes if OP666 is found throughout the texts and ends with any three characters; otherwise returns No.

Things to Remember

📌 You can use any of the two wildcards, an asterisk(*) or a question mark sign(?).


Conclusion

To sum up, we have discussed 5 methods to check if a cell contains partial text in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Read More

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

2 Comments
  1. Hi, thanks for sharing this great tutorial. I have a question, how do you add multiple countifs in the same formula ? For example, if a cell contains some text, then display this…but if it contains another text, then display something else ? thanks!

    • Hi CHRIS,
      Thanks for this interesting question. It’s not about adding multiple COUNTIFS functions but multiple COUNTIF functions inside one IFS function.

      Look at the following formula. It will look for two keywords “MTT” and “GL” across the text. If it finds MTT then the output will be “MTT Exists!”. For “GL” the output will be “GL Exists!”.
      If nothing matches, it will return “No Results Found!”.

      =IFERROR(IFS(COUNTIF(B5,”*MTT*”),”MTT Exists!”,COUNTIF(B5,”*GL*”),”GL Exists!”),”No Results Found!”)

      Regards!

Leave a reply

ExcelDemy
Logo