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:

`=IF(COUNTIF(B5,"MTT*"),"Yes","No")`

- 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:

`=IF(COUNTIF(B5,"*NPP"),"Yes","No")`

- 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:

`=IF(COUNTIF(B5,"*NQ*"),"Yes","No")`

- 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:

`=IF(COUNTIF(B5,"?1VX40NQ"),"Yes","No")`

- 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**:

`=IF(COUNTIF(B5,"OP666???"),"Yes","No")`

- 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**

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!