Alok Paul
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.
- 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 function if you have to meet just a single criterion. If you have multiple criteria, then you have to use the SUMIFS 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