How to Count Words in Excel

Excel is a tool for storing, organizing, and visualizing a large volume of data. It uses a rectangular block known as a cell to store the data unit. This tool can be used to perform different tasks like creating graphs and analyzing trends, to get insights from the data. Data analysis can be performed in many ways like visual analysis using graphs, mathematical or trend analysis using formulas, etc. It is primarily used for financial data analysis by accounting professionals but can be used by everyone, as it is very easy to use and saves a lot of time. Let’s see how to count the number of words in a cell or range of cells.

Unfortunately, Excel doesn’t come with a built-in utility for word counts. Fortunately, you can create more complex formulas to perform almost any job by combining a number of functions. And we’ll apply this strategy to Excel’s word count.

How to Check the Number of Words in a Cell

Let’s consider a scenario to count the total number of words present in a cell. As Excel doesn’t come with a built-in tool that we could directly use to count the words in a cell, We’ll be using different steps to achieve the desired outcome, by combining different formulas. Below are the steps we are going to follow:

1.  Firstly, we will remove any extra spaces present at the start or at the end of sentences or text data present inside the cell.
2.  Calculate the total number of characters present, including the spaces present in between the words.
3.  Now, we will replace all the white spaces that are present in between words and then count the characters.
4.  Calculate the difference between the total count of characters with spaces to that without spaces (step 2 – step 3). This will give the count of the total number of white spaces.
5.  Add 1 to the difference to get the count of words.

For demonstration, we will take one string- “Hello!, welcome to GeeksforGeeks.” and calculate the number of words present in it.

Step 1: Remove white spaces at the beginning or the end of the string

TRIM(): This function takes a cell with content and as a result, returns the same text without any white space at the beginning or end.

Input: ”          Hello, How are you?”

Output: Hello, How are you?

Output

Step 2: Count the number of characters with white spaces between the words

LEN(): This function calculates the number of characters present in the cell.

Output

Step 3: Replace the spaces with no space and then calculate the number of characters

SUBSTITUTE(): This function replaces a piece of text with another bit of text.

Syntax: 

SUBSTITUTE ( cell name, old_text, new_text)

Output

We can see that the spaces are removed.

To count the characters, we can add the LEN function with the substitute function line LEN(SUBSTITUTE(cell_name)).

Output

Step 4: Calculating the Difference 

Output

Step 5: Add 1 to the difference value obtained

Output

The count of words in “Hello!, Welcome to GeeksforGeeks” is 4. Therefore, the formula to calculate the count of words in a cell is:

= LEN(TRIM(cell_name)) – LEN(SUBSTITUTE(A2, ” “, “”) + 1

How to Count the Total Number of Words in a Range

The formula that calculates the total number of words in a cell can be embedded within the SUM function to determine how many words are present in a specific range.

Syntax:

=SUM(LEN(TRIM(cell_range))-LEN(SUBSTITUTE(cell_range,” “,””))+1)

Example:

Suppose we have text in three cells, and we want to calculate the total words present in those cells.

Formula

Use the Formula below: 

=SUM(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4,” “,””))+1)

Output

How to Count Specific Words in a Range

If you want to count the recurrence of a particular word or text within a range of cells, use a similar approach – take the formula to count specific words in a cell, and combine it with the SUM.

=SUM((LEN(range)-LEN(SUBSTITUTE(range, word,””)))/LEN(word))

As the above formula is an array SUM formula, Press Ctrl+Shift+Enter to correctly implement it.

Example 

To count all occurrences of the word entered in cell C1 within the range A2:A3, use this formula:

=SUM((LEN(A2:A3)-LEN(SUBSTITUTE(A2:A3, C1,””)))/LEN(C1))

As, SUBSTITUTE is a case-sensitive function, and therefore the above formula distinguishes between uppercase and lowercase text:

To make the formula case-insensitive, use either the UPPER or LOWER function:

=SUM((LEN(A2:A3)-LEN(SUBSTITUTE((UPPER(A2:A3)),UPPER(B2),””)))/LEN(B2))

Or

=SUM((LEN(A2:A3)-LEN(SUBSTITUTE((LOWER(A2:A3)),LOWER(B2),””)))/LEN(B2))

In this article, I’ve shown you how to count words in Excel. Hopefully, this article will prove to be of help to you, I advise you to read more articles on Excel if you want to understand more. I appreciate your time and look forward to hearing from you soon!

FAQs on How to Count the Number of Words in a Cell

Q1: How to count the Occurence of a Single character in a cell?

Answer:

To count how many times a specific character appears in a cell, Follow the below steps:

Step 1: Idnetify the cell in which you want to count the characters.

Step 2: Now type the formula ‘=LEN(cell_ref)- LEN(SUBSTITUE(cell_ref,”a”, “”)’

Attributes

“Cell_ref” is the cell which is containing the text.

“a” can be any character you want to count.

Q2: How to Count the occurrence of a Single Character in a Range of Cells?

Answer:

To count how many times a specific characters appears ina range of cells, follow the below steps:

Step 1: Select the range of cells in which you want to count the characters.

Step 2: Now use the formula : ‘=SUM(LEN(range) – LEN(SUBSTITUTE(range,”a”,””)))’.

Where ‘range’ is the range of cells you want to analyze

‘a’ is the character you want to count.

Q3: How can we Count the number of times a specific word appears in a row or a Column?

Answer:

We can count the occurrence of a specific word in a row or column using teh COUNTIF Function. First, name the row and column using Excel’s “Define name ” Feature. Then, Use the COUNTIF Fucntion with the named range to count the occurrence of the word.

‘=COUNTIF(column_name, “a”)’

Where Column_name is the name you assigned to the row or column and “abc” is the word you want to Count.

Q4: What are the alternative way to count the words in Excel?

Answer:

Other than formulas and VBA, you can also use Excel’s Power Query Feature to Count words in range of cells. Power Query allows you to perform various data manipulations, including text analysis.