Easy Steps on How to Count The Repetition of a Word/s in ExcelGuide with Multiple Methods

Written By:
Adiste Mae

Reviewed By:
FundsNet Staff

It is common when working on a project with a lot of datasets to count how many times a specific value or word appears.

Sometimes it is needed to count how many words appear in a specific cell, column, row, or even the whole worksheet.

In this problem, there might be two kinds of requirements that you will need to check – the number of times words appear in a cell, or the number of times a word is used within a single cell.

This tutorial will provide a step-by-step process of checking the two requirements mentioned above that also includes the use of VBA. 

Using COUNTIF on Counting a Specific Word in a Range

The main function of the COUNTIF function is to count the number of times a given condition is met.

Matching the specific value to the cell value in a range is one of the common uses of this function.

Therefore, it may be in the form of a number or a string.

The formula of the COUNTIF function is shown in the table below:

Whereas

• range is the range of cells needed to match and count from
• condition is the condition that must be met in order to count a cell as a match

This function will conduct an automatic search for the rest of the cells in a given range based on its given condition.

If the function finishes searching all the cells in the given range, this function will return the number of matched cells.

Here is a simple example of this problem. Let’s count the number of times the word “USB A cable” will appear in the range A2:A10:

Here’s the step-by-step process on how we can apply the COUNTIF function in our example:

1. Select a cell where you want the counted words to appear. In our case, we are putting it in cell D2.
2. In the selected cell, input the formula that is shown earlier. Here’s our formula: COUNTIF(A2:A10,“USB A Cable”)
3. Press the Enter Key.

This will now search for the word “USB A Cable” in a given cell within a specific range. 

Note:

The COUNTIF function only counts the exact word “USB A Cable” in the range provided.

If the word in the cell does not match the word we are counting, it will be invalid. 

For example in the given range of cells, there is the word “USB A Cable USB A Cable”. This word will not be counted.

Using LEN and SUBSTITUTE on Counting a Specific Word in a Cell

The method above is best when you are looking for the exact word in a range of cells.

But this method is not that reliable if you are looking for a specific word in a string of words in a cell.

For our example, we are given a sentence in a single cell and you want to look at and count the word cable that appears in that cell.

Here is our example in the image below:

There are other methods for solving this problem.

You can use the LEN and SUBSTITUTE functions in this scenario.

This can be a combination of formulas in order to solve this problem.

Using VBA code may be an option for doing this method quickly.

What is the LEN Function?

This is a function that looks at the length of a string or the number of characters, text, or numbers in a string.

Here’s the syntax for the LEN function:

Where a string could be a word to a cell containing a text which you are looking for.

What is the SUBSTITUTE Function?

The SUBSTITUTE function is for removing a certain word from a given string of text.

Here’s the syntax for the SUBSTITUTE function:

Where

• original_string is the cell reference that you are working
• old_text is the word or text that you are replacing
• new_text is the word or text that you are replacing the old_text

Combining the LEN and SUBSTITUTE Function

The function mentioned above is not that reliable when individually counting how many times a word appears in a cell.

But if you correctly combine the two functions together, you can easily count the number of words that appear in a cell within a range.

This is the combined formula for counting how many words appear in a cell:

In this combined formula, the “word” is the word you are counting. And the “cell_reference” is the reference to the range, column, or cell where you want to count the word.

Here is the step-by-step process of applying the formula given above:

1. Select a cell where you want the counted words to appear. In our case, we are putting it in cell B5.
2. In the selected cell, input the formula that is shown earlier. Here’s our formula:

       3. Press the Enter Key.

Now, this method will give you the result of counting how many times the word “cable” appears in cell A2.

By adding the dollar sign ($) you can fix the reference to cell A2. Now, you can copy the same formula in cell A2.

This is how you can easily copy the formula for the other text.

Drag down the fill handle to the column below.

How does the Formula Work?

Here’s the breakdown of the formula:

1. The initial step is to replace the word “cable” with a blank character by using the SUBSTITUTE function: SUBSTITUTE(A2, “cable”,””).
2. the Second step is to find the length of the original string without the occurrence of the word “cable” by using the LEN function: LEN(SUBSTITUTE(A2, “cable”,””)).
3. Third, we deducted this length from the length of the original text string which is with the words “cable”: LEN(A2)- LEN(SUBSTITUTE(A2, “cable”,””)).
4. This is the total number of words “cable”. This is a 5-letter word, if the word occurs 3 times, the result is 15.
5. Lastly, the result is divided by the length of the word “cable”: =(LEN($A$2)-LEN(SUBSTITUTE($A$2, A5,””)))/LEN(A5).

Based on the above, we get 15/5=3. Now, it is interpreted that the word “cable” appears 3 times in cell (A2).

Note:

This function is relatively case-sensitive so we need to make sure that the word is exactly the same as that we are counting. Otherwise, it will not count the words with capitalization. 

The best way to deal with this is to make sure to check everything is converted to small letters. To make the function not case-sensitive, try using this converted formula below:

Where:

The LOWER function is for converting the original and new text to lowercase (small letters).

Counting of How Many Times a Word Appears in a Range

The method mentioned above is only applicable in a single cell.

But what if you are looking for the counting of how many words in multiple cells?

To address this problem, you must use the SUMPRODUCT function and add it to the formula above. Here’s the formula:

Where:

The SUMPRODUCT function makes sure that you use this in each cell in the chosen range.

Here is an example of how this is used in the image below.

The chosen cells are A2 and A3, and we are counting how many times the word “cable” appears in the cells:

Here’s the step-by-step process:

1. Select a cell where you want the counted words to appear. In our case, we are putting it in cell B6.
2.  In the selected cell, input the formula that is shown earlier. Here’s our formula:

      3. Press the Enter Key.

This will show you how many times the word “cable” appears in cells A2 and A3.

By adding the dollar sign ($) you can fix the reference to cells A2 and A3. Now, you can copy the same formula in cells A2 and A3.

This is how you easily copy the formula for the other text.

Drag down the fill handle to the column below.

Using VBA

VBA can also help you count how many times the word appears.

The code is shown below. You can copy and paste it.

Now, here’s the step-by-step process:

1. If you are not able to see the Developer Ribbon, right-click at any of the tabs at the menu bar. A dialog box will pop up. Under the Main Tabs, check mark ‘Developer’. Hit OK. An additional tab will be added at the Menu bar – the Developer Tab. Or, try going to the File Menu, then Options. Then, select Customized Ribbon and check the Developer option in its Main Tabs. Then lastly click the OK Button.
2. Go to Developer Menu Ribbon between the View Tab and Help Tab. Then, select Visual Basic.
3. The VBA window will open, then click Insert->Module. The coding process now starts. Just copy and paste the code script mentioned above. Then Run the code.
4. Now, click the cell/s you want to count which contains the text. 
5. Go to Developer->Macros->count_word_occurences->Run.
6. Now you will see how many times the word “cable” appears in the message box.

This method works whether you want to select single or multiple cells.

Here are some ways in editing your code, depending on what requirement you are working on:

• By changing line 3, you can easily change the words that you want to count. For example, you want to count the word “because”. Your line 3 should look like this: search_word = ”because”
• You can also choose the cell for your reference instead of manually typing it. Let’s assume that we have cell A6 which contains the word “happy”. Instead of manually typing “happy”. Just select cell A6 in line 3: search_word = Cells(6,”A”).Value
• You can also directly display the result in a certain cell instead of seeing it in a message box. Let’s assume we want to display it in cell B6, then replace line 100 to: Cells(6,”B”).Value = Count
• Then if you want the VBA code to be case-sensitive, then you can use the LCase function. Just replace line 8 to: Count = Count + ((Len(cell) – Len(Replace$(LCase(cell), LCase(search_word), “”))) / Len(search_word))

Conclusion

We showed how we can count the number of times a word appears in Microsoft Excel.

We showed you how we can use different methods for these problems.

By using the COUNTIF function, we can determine the exact text we want to count in a range of cells.

The SUBSTITUTE and LEN function shows us how we can count how many times a certain word appears inside the string of a single cell.

Then, we also show how to use the SUMPRODUCT function in counting the words in the entire range of cells that contain stings, by adding the same formula that we used.

And lastly, we are able to learn how to use VBA code.

Hope this tutorial helped you a lot.