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.


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.

LEN and SUBSTITUTE Functions in Excel to count specific words

  • Now, put the following formula based on the LEN and SUBSTITUTE functions on Cell E5.
=(LEN(B5)-LEN(SUBSTITUTE(B5,D5,"")))/LEN(D5)

LEN and SUBSTITUTE Functions in Excel to count specific words

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)

Excel UPPER Function with the Combination of LEN and SUBSTITUTE Functions to count specific words

  • Press the Enter button to get the result.

Excel UPPER Function with the Combination of LEN and SUBSTITUTE Functions to count specific words

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.

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

πŸ“Œ Steps:

  • Insert the following formula on Cell E5.
=SUM(--(SUBSTITUTE(","& SUBSTITUTE(B5," ","") & ",", "," & $D$5&",","", SEQUENCE(LEN(B5))) <> "," & SUBSTITUTE(B5," ","")&","))

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

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

Excel Formula to Count Specific Words in a Column or Range

  • Then, insert the following equation on Cell E7.
=SUMPRODUCT((LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,E7,"")))/LEN(E7))

Excel Formula to Count Specific Words in a Column or Range

  • Press the EnterΒ button.

Excel Formula to Count Specific Words in a Column or Range

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

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

Leave a reply

ExcelDemy
Logo