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

We will be using a sample product price list as a dataset to demonstrate all the methods.

Method 1 – Check If Cell Contains Partial Text at the Beginning

Let’s check for “MTT” at the start of the cell value:

• Select cell E5.
• Copy this formula formula:

• Press Enter.

• Drag the Fill Handle icon to the end of the Partial Text column.

You should have the same result as in 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.

Method 2 – Check for Partial Text on the End

Let’s check for “NPP” at the end of a string inside cells:

• Select cell E5.
• Copy-paste the following formula into it:

• Hit Enter.

• 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 result like the picture below:

␥  Formula Breakdown

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

Method 3 – Check For Partial Text at any Position

Let’s check for “NQ” anywhere in the cells:

• Select cell E5 and copy the formula given below:

• Hit Enter.

• Drag the Fill Handle icon to the end of the Partial Text column.

␥  Formula Breakdown

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

Method 4 – Check for Partial Text with Starting Wildcard Character at the Front

Let’s mark all the cells that contain 1VX40NQ after any one character:

• Select cell E5 and paste the following formula:

• Hit Enter.

• Drag the Fill Handle icon to the end of the Partial Text column.

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

Method 5 – Check for Partial Text with Wildcards Behind It

Let’s look for all the cells having the partial text “OP666” and ending with any three characters:

• Type the following formula into E5:

• Press Enter.

• Drag the Fill Handle icon to the end of the Partial Text column.

␥  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(?). The asterisk denotes zero or more characters, but the question mark replaces only a single one.

Download the Practice Workbook

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