# Excel Formula to Count Specific Words in a Cell (3 Examples)

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.

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

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

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

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

## Related Articles #### Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts 