Counting how many times a certain term or number appears is a common requirement when working with large datasets.

It’s possible that you’ll need to know how many times a term appears in a cell, a column, or the entire worksheet.

There are two types of problems that can occur. You may need to count the number of cells that contain a specific term or the number of times a specific word appears in a cell.

We’ll look at both types of problems and how to address them in this article, including using VBA.

A simple COUNT() function or a filtered data set may easily count duplicates, which is a common Excel chore. If the counts are more important than the values themselves, the task may be more difficult.

For example, you might want to know how many times a particular value appears once, twice, three times, four times, and so on. In other words, a conditional count of a simple count is required.

Fortunately, the issue simply appears to be complicated. I’ll show you a basic approach that uses a helper column and a simple matrix in this article. I’ll also show you how to solve an expression problem.

- Count a Specific Word in a Cell using LEN and SUBSTITUTE
- Using countif to Count a specific word in a range
- Counting how many times a word appears in a range
- Using vba to count the number of times a word appears in any range

## Count a Specific Word in a Cell using LEN and SUBSTITUTE

If you’re looking to find the number of times a word appears or occurs inside the string of a cell, the LEN and SUBSTITUTE function when combined cleverly can help you do this easily and in a simple manner.

A typical example: we want to find the number of times the word ‘is’ appears in a string. To solve this

Because this is a very short list, we can manually count and tell how many times the word appears. But what if we have to check this from a long list, or thousands of columns.

The task becomes hectic and convocated. In this tutorial; the LEN function and the SUBSTITUTE function will be used to explain how this could be done in a very simple manner.

The LEN function is used to find the length of a string (a text or reference cell containing for which you would like to find the length)

The syntax for the LEN function is

[code] =LEN(*string*) [/code]

The substitute function is used to replace a specific word in a string with another.

The syntax for the function is

[code]

=SUBSTITUTE(original_string, old_text,new_text)

[/code]

In this syntax: The text or cell reference you want to work on is called *original_string.*

The word or substring you want to replace is called *old_text.*

The word or substring you want to replace the old text with is *new_text.*

**Both of the following functions don’t appear to have anything to do with counting the number of times a word appears in a cell on its own. However, when cleverly put into a formula, they successfully complete the objective.**

**We can use the formula to count how many times a word appears in a cell:**

[code] =(LEN(*cell_reference*)-LEN(SUBSTITUTE(*cell_reference,word*,””)))/LEN(*word*) [/code]

The word you want to count is ** word**, and

*cell_reference**is the reference to the cell you want to count from is cell reference.*

The example below will help make this clearer.

To achieve your desired results follow the steps below carefully so you don’t miss a step.

- Choose or select the cell you want to have the number in (B10 IN OUR CASE)
- In this chosen cell, type the formula: [code]
**=(LEN($A$3) -LEN(SUBSTITUTE($A$3, $A$10,””)))/LEN($A$10)**[/code]

This gives you the number of times the word “is” appears in the sentence in cell A3.

The effects of the dollar sign ($) are to fix the reference to the cell. This would help you apply the same formula to count other words in cell A.

**To comprehend how this formula worked, we must first dissect it:**

To comprehend how this formula worked, we must first dissect it:

- To begin, we used the SUBSTITUTE function to replace the word “is” with a blank in the original string: A3, “is”,””) SUBSTITUTE(A3, “is”,””) SUBSTITUTE(A2, “is”,”
- After that, we used the LEN function to determine the length of the original string that did not contain the word “happy”: SUBSTITUTE(A3, “is”,””) LEN(SUBSTITUTE(A3, “is”,””) LEN(SUBSTITUTE(A3, “
- After that, we reduced this length from the original string’s length (which contained the words “happy”): LEN(A3, “is”)- LEN(SUBSTITUTE(A3, “is”)- LEN(SUBSTITUTE(A3, “is”)- LEN(SUBSTITUTE(A2, “is”, “ ”)
- This returns the total number of characters used in all instances of the word “is.” Because it’s a two-letter word, the above operation will produce 8 if it appears four times.
- Finally, the length of the word “happy” is split by the value: =(LEN($A$3)-LEN(SUBSTITUTE($A$3, A10,””))/LEN (A10).
- This will tell you how many times the word “is” appears in the original text. As a result, we obtain 8 / 2 = 4. In cell A3, the word “is” appearing four times.

It is worthy to note that Case is important when using the SUBSTITUTE function. As a result, this method will only count how many times the word “is” appears in small characters. You would not receive the correct answer if you searched for the word in all uppercase.

So make sure everything is converted into the lower case by using this formula below:

[code]=(LEN(*cell_reference*)-LEN(SUBSTITUTE(LOWER(*cell_reference*), LOWER(*word*),””)))/LEN(*word*)[/code]

Both the old and new text are converted to lowercase using the LOWER function.

## Count How many times a Word Appears in a Range

The approach described above can only be used to determine the number of times a word appears in a single cell. But what if you wanted to see how many times the same term appears in multiple cells’ strings?

You’ll need to wrap the SUMPRODUCT function around the above calculation to accomplish this. If you’re working with a group of cells, your formula should be:

[code]=SUMPRODUCT(LEN(*cell_range*)-LEN(SUBSTITUTE(*cell_range*, *word*,””)))/LEN(*word*))[/code]

The SUMPRODUCT function guarantees that a collection containing the count for each cell in the range is returned.

For example, let us say you have texts strings (A3:A4:A5) and you want to find out how many times the word ‘is’ appears in all of them.

Follow these simple procedures to achieve this

Choose the cell where you wish to enter the count (cell B6 in this case).

Type the following formula in this cell:

[code]=SUMPRODUCT((LEN($A$3:$A$4:$A$5)-LEN(SUBSTITUTE($A$3:$A$4:$A$5,A10,””)))/LEN(A10))[/code]

And press the enter key. This in return will give you the number of times ‘is’ appears in the range A3:A4:A5.

## Counting the Number of Times, a Word Appears in any Range Using VBA.

VB Script can be used to do the same operation as above.

Here’s the VBA code we’ll use to count how many times the word “happy” appears throughout the code. Feel free to copy and paste it.

[code]Sub count_word_occurrences() Count = 0 search_word = “is” Dim rng As Range Dim cell As Range Set rng = Application.Selection For Each cell In rng Count = Count + ((Len(cell) – Len(Replace$(cell, search_word, “”))) / Len(search_word)) Next cell MsgBox (“The string ” & search_word & ” occurred ” & Count & ” times”) End Sub[/code]

Follow the steps below carefully to execute this line of code.

- Select Visual Basic from the Developer Menu Ribbon.

- Click Insert->Module once your VBA window has opened. You can now begin coding. In the module window, type or copy-paste the above lines of code. Your program is now ready to execute.

- Choose one or more cells that contain the text you want to count from.
- Go to Developer->Macros->count word occurrences->Run to get started.

A message box will pop up, giving you how many times, the word “is” appears in your chosen range.

**It is great to note that the beautiful thing about this strategy is that it does not matter whether you select one cell or a range of cells. It works in both directions.**

## Count a Specific Word in a Range using COUNTIF

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

One of the most typical applications of this method is to match a specific value to a range of cell values. This value can be an integer or a string.

The COUNTIF function has the following syntax: **COUNTIF( range, condition)**

The above code will look for all of the cells in the provided range and count all of them that match the condition. It will return the number of matched cells once it has finished looking through all of the cells in the range.

Let’s take a look at how this function works in practice. Let’s say we want to see how many times the name ‘butter’ appears in the range A3:A11, as shown below:

Here are the steps to using the COUNTIF function to solve the problem:

Choose the cell where you wish to enter the count (cell C4 in our case).

Type the following formula in this cell:

[code]=COUNTIF(A3:A11, “butter”) [/code]

and press the enter key.

This displays the number of times the word “butter” appears in the range of cells A3:A11.

Note that this method only counts the number of cells in which the word “butter” appears exactly once. If one of the cells, for example, includes the words “butter butter,” the COUNTIF function would not consider the cell to be a match and hence would not count it.

**This tutorial was created with a variety of concerns in mind while trying to count the number of times a word appears on your worksheet. We hope you found this information useful.**

## Leave a Comment