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:
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")❸ Press the ENTER button.
❹ 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,”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: How to Highlight Cell If Contains Text in Excel
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")❸ Press the ENTER button.
❹ 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.
Read More: How to Use VLOOKUP If Cell Contains a Word within Text in Excel
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")❸ Press the ENTER button.
❹ 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.
Read More: How to Return TRUE If Cell Contains Text in Excel
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")❸ Press the ENTER button.
❹ 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.
Read More: How to Use IF-THEN Statements with Text in Excel
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")❸ Press the ENTER button.
❹ 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.
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!