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.


Check If Cell Contains Partial Text in Excel: 5 Ways

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.

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.

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(?).


Download the Practice Workbook


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.


<< Go Back to Text | If Cell Contains | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo