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:

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:

❸ 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 Use VLOOKUP If Cell Contains a Word within 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:

❸ 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: If Cell Contains Text Then Return Value in Another Cell Using Excel Formula

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:

❸ 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 Value in Excel If Cell Contains Text from List

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:

❸ 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: If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)

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:

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

Read More: How to Sum If Cell Contains Specific Text in Excel (6 Ways)

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.

Related Articles

• If Cell Contains Text Then Add Text in Another Cell in Excel
• Excel If Cell Contains Text Then Return Value (8 Easy Ways)
• Find Text in Excel Range and Return Cell Reference (3 Ways)
• Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
• If Cell Contains Text Then Copy to Another Sheet in Excel
• How to Assign Value If Cell Contains Word in Excel (4 Easy Ways)