Count Number of Times a Word Appears in a Cell – Excel & Google Sheets

This tutorial demonstrates how to count the number of times a word appears in a cell.

count times word appears in cell Main Function

Count Specific Word

To count the number of times a word appears in a cell, we will use the LEN and SUBSTITUTE Functions.

count times word appears in cell 01 New

In the example above, our objective is to count the number of times the word “one” appears in each cell.

The formula calculates the length of the original cell and then gets the length of the cell after substituting the word to be counted with a blank, subtracting the two lengths. The result is then divided by the length of the word, this gives us the number of times a word appears in a cell.

Let’s walk through the formula by breaking it down to its individual functions.

The LEN Function

First, we will use the LEN Function to count the total number of characters in the text string

count times word appears in cell 02

The formula above returned the count of the characters in the text string including the spaces.

AutoMacro - VBA Code Generator

The SUBSTITUTE Function

Next, we will use the SUBSTITUTE Function to remove the word “one” from the text string.

count times word appears in cell 03

The SUBSTITUTE Function is case sensitive, so the word needs it to be in the same case (upper or lower case) as it is in the cell for it to be substituted.

Using the formula above, every instance  of the word “one” in the text strings  was replaced with a blank.

The Length of Substituted Text

Now we can use the LEN Function to count the number of characters left, after removing all instances of the word (“one”) from the text.

count times word appears in cell 04

The Length of Characters Removed

To determine the number of characters that were removed from the original text. We can take the difference between the two LEN Function results.

count times word appears in cell 05

Dividing this by the character length of the word (“one” has 3 characters), gives us the number of times the word appears in each cell.

count times word appears in cell 06

Now that we’ve gone through the individual parts of the formula, bringing it all together gives us our initial formula. It returns the number of times the word “one” appears in each cell.

count times word appears in cell 01 New

Count Times Word Appears In Cell in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

count times word appears in cell Google Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!