How to count how many times a word appears in Excel

Return to Excel Formulas ListCount Number of Times a Word Appears in a Cell Excel & Google SheetsDownload Example WorkbookDownload the example workbookThis tutorial demonstrat

How to count how many times a word appears in Excel

Return to Excel Formulas List

Count Number of Times a Word Appears in a Cell  Excel & Google SheetsDownload Example Workbook

Download the example workbook

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

Count Specific Words

To count the number of times a word appears in a cell, we will use the LEN and SUBSTITUTE Functions.=(LEN(B3)-LEN(SUBSTITUTE(B3,E3,"")))/LEN(E3)

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.

Lets 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=LEN(B3)

count times word appears in cell 02

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

The SUBSTITUTE Function

Next, we will use the SUBSTITUTE Function to remove the word one from the text string.=SUBSTITUTE(B3,F3,"")

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.=LEN(D3)

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.=LEN(G3)

count times word appears in cell 06

Now that weve 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.=(LEN(B3)-LEN(SUBSTITUTE(B3,E3,"")))/LEN(E3)

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 sheets

Video liên quan