A word can be present in a cell multiple times. There are multiple options to find and count that specific word from a cell in** Excel** including the usage of **Excel** formulas. In this article, we will discuss **3** different examples of **Excel **formula to **count specific words** in a cell with proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**3 Examples to Count Specific Words in a Cell with Excel Formula**

We will discuss **3 **formulas to count specific words in a cell in **Excel**. We will use the following data set that contains the **Name **of some books with the authors.

**1. Combination of LEN and SUBSTITUTE Functions (Compatible with Case-Sensitive Data)**

**The LEN function **returns the number of characters of a text string.

**The SUBSTITUTE function** replaces existing text with new text in a text string.

In this section, we will apply a formula based on the **LEN **and **SUBSTITUTE **functions.

**📌 Steps:**

- We add two new columns to the dataset, named “
**Word**”, and “**Count**”.

The **Word **column is to input the desired word and the **Count **column to show the occurrence number of the specific word.

- We want to find the word
**“The”**from each cell of our dataset. Input this word on each cell of the**Word**column.

- Now, put the following formula based on the
**LEN**and**SUBSTITUTE**functions on**Cell E5**.

`=(LEN(B5)-LEN(SUBSTITUTE(B5,D5,"")))/LEN(D5)`

We get the occurrence number after the implementation of the formula.

- Now, press the
**Enter**button.

- Drag the
**Fill Handle**icon downwards.

We get the result for all the cells.

**🔎 Formula Explanation:**

**SUBSTITUTE(B5,D5,””)**

This replaces the value of **Cell D5** with nothing from **Cell B5**.

**Result: ” Commonwealth of Cricket “**

**LEN(SUBSTITUTE(B5,D5,””))**

Determines the length of the previous section.

**Result: 24**

**LEN(B5)**

Determines the length of **Cell B5**.

**Result: 27**

**LEN(B5)-LEN(SUBSTITUTE(B5,D5,””))**

A subtraction operation is performed here.

**Result: 3**

**LEN(D5)**

Determines the length of **Cell D5**.

**Result: 3**

**(LEN(B5)-LEN(SUBSTITUTE(B5,D5,””)))/LEN(D5)**

Divide the last value by the length of **Cell D5**.

**Result: 1**

**Read More:** **How to Count Words in Excel with Formula (2 Handy Examples)**

**2. Add UPPER Function with LEN-SUBSTITUTE Combination**

The first formula was case-sensitive. So you will not get a proper result if you are not supposed to consider case sensitivity.

**The UPPER function** converts a text string to all uppercase letters.

In this formula, we will add the **UPPER **function with the combination of the **LEN **and **SUBSTITUTE **functions. The **UPPER **function will convert the text of the cells and the searching word in the same uppercase format first. Then, count the number of occurrences.

Our desired word is present in different cases on the dataset.

**📌 Steps:**

- Now, put the formula on
**Cell E5**of the**Count**column.

`=(LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),UPPER(D5),"")))/LEN(D5)`

- Press the
**Enter**button to get the result.

We get the occurrence number.

- Drag the
**Fill Handle**icon now.

Get the result for the whole column.

Alternatively, we can use **the LOWER function** to replace the **UPPER **function. This will convert all texts to lowercase and then count the occurrence number.

`=(LEN(B5)-LEN(SUBSTITUTE(LOWER(B5),LOWER(D5),"")))/LEN(D5)`

We get the same result using the **LOWER **function.

**🔎 Formula Explanation:**

**UPPER(B5)**

Converts all the texts of **Cell B5** to uppercase.

**Result: THE COMMONWEALTH OF CRICKET**

**UPPER(D5)**

Converts all the texts of **Cell D5** to uppercase.

**Result: THE**

**SUBSTITUTE(UPPER(B5),UPPER(D5),””)**

This replaces the value of **Cell D5** with nothing from **Cell B5** in uppercase.

**Result: ” COMMONWEALTH OF CRICKET “**

**LEN(SUBSTITUTE(UPPER(B5),UPPER(D5),””))**

Determines the length of the previous section.

**Result: 24**

**LEN(B5)**

Determines the length of **Cell B5**.

**Result: 27**

**LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),UPPER(D5),””))**

A subtraction operation is performed here.

**Result: 3**

**LEN(D5)**

Determines the length of **Cell D5**.

**Result: 3**

**(LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),UPPER(D5),””)))/LEN(D5)**

Divide the last value by the length of **Cell D5**.

**Result: 1**

**Read More:** **How to Count Words in Excel Column (5 Useful Ways)**

**3. Combine SUM, SEQUENCE, and SUBSTITUTE Functions to Count Specific Words in a Cell**

**The SUM function** adds all the numbers in a range of cells.

**The SEQUENCE function** returns a sequence of numbers.

We will use a different dataset for this section. Each word in the cells is separated by **Comma (,)**. We will find our specific words from each cell.

**📌 Steps:**

- Insert the following formula on
**Cell E5**.

`=SUM(--(SUBSTITUTE(","& SUBSTITUTE(B5," ","") & ",", "," & $D$5&",","", SEQUENCE(LEN(B5))) <> "," & SUBSTITUTE(B5," ","")&","))`

- Now, press the
**Enter**button and drag the**Fill Handle**icon downwards.

We get results for each cell.

**Read More:** **How to Count Specific Names in Excel (3 Useful Methods)**

**Excel Formula to Count Specific Words in a Column or Range**

We have discussed 3 Excel formulas to count specific words in a cell. Now, we will see how to do the same thing in case of column or range.

**The SUMPRODUCT function** returns the SUM of products of corresponding ranges or arrays.

In the previous section, we get the number of occurrences for each cell separately. Here, we want to get the total number of occurrences for a range or column. We simply add the **SUMPRODUCT **function with the formula of the **1st** method and replace the cell with range.

**📌 Steps:**

- We add two cells to input the specific word and total occurrence number.

- We input the word
**“The”**in**Cell E5**.

- Then, insert the following equation on
**Cell E7**.

`=SUMPRODUCT((LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,E7,"")))/LEN(E7))`

- Press the
**Enter**button.

We get the result.

**🔎 Formula Explanation:**

**SUBSTITUTE(B5:B11,E5,””))**

This replaces the value of **Cell E5** from each cell of the **Range B5:B11**.

**Result: ** **[Commonwealth of Cricket, ****Little Book of Encouragement, Handmaid’s Tale by, Time of Uprooted, {Tower, Zoo, and Tortoise}, Teeth of Tiger, Stone Angel]**

**LEN(SUBSTITUTE(B5:B11,E5,””))**

Determines the length of each cell of the previous section.

**Result: [24, 30, 20, 18, 27, 16, 12]**

**LEN(B5:B11)**

Determines the length of each cell of the range** B5:B11**.

**Result: [27, 33, 23, 24, 36, 22, 15]**

**LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,E5,””))**

Determines the length of each cell of the range** B5:B11**.

**Result: [3, 3, 3, 6, 9, 6, 3]**

**(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,E5,””)))/LEN(E5)**

Determines the length of each cells of the range** B5:B11**.

**Result: [1, 1, 1, 2, 3, 2, 1]**

**SUMPRODUCT((LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,E5,””)))/LEN(E5))**

Total sum of the previous section.

**Result: 11**

**Conclusion**

In this article, we showed **3** methods to count specific words in a cell using **Excel **formula. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.

Good Evening, I want to say thank you for your website as I try to increase my knowledge and understanding of excel and its many useful functions have helped me to understand things better.

I think I will be able to figure out how to sum multiple numbers that belong to a particular step.

I have a report that will only give me the highest number it doesn’t recognize the other two entries and doesn’t sum them together. I could manually do it but that will take the time that could be better used. I included what I am trying to solve below. I am still learning so it might take me a few tries. Thanks and have a great evening and keep up the great work.

Received: 0 Sent to Vendor: 14 Sent to Vendor: 36 Sent to Vendor: 16 Awaiting QA: 1 Complete: 3 Awaiting QA: 0

Hi BARNEY! Hope you are doing well. Thanks for your nice compliments. We are happy to know that the readers find our articles useful.

However, the problem you are facing is not quite clear from what you have told us. Are you trying to sum up entries that meet specific criteria? In that case, you have to use the

SUMIF functionif you have to meet just a single criterion. If you have multiple criteria, then you have to use theSUMIFS function. There are more articles in our blog related to these functions. To explore them, scroll down and click on the function tag names.If this is not what you are looking for, please let us know more details. You can send me the problem with a sample file at

[email protected]or at[email protected].Best wishes. Keep staying with us.

-Alok Paul

ExcelDemy Team