Word Count in Excel

Publication Date :

26 Apr, 2021

Blog Author :

Edited by :

Reviewed by :

Download FREE Word Count Excel Template and Follow Along!

Word Count Excel Template.xlsx

Table Of Contents

Count Words in a Cell of Excel

There is no in-built excel formula to find the Word Count and therefore, it needs to be entered manually. You can make use of the formula below to calculate wordcount in excel -

=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1

Let us understand the working of this formula.

To begin with, we utilize the SUBSTITUTE function to evacuate and displace all spaces in the cell with a vacant content string ("). The LEN function restores the length of the string without spaces.

Next, we subtract the string length without spaces from the absolute length of the string. The number of words in a cell is equivalent to the number of spaces plus 1. So, we add 1 to the word count.

Further, we utilize the TRIM function to remove extra spaces in a cell. A worksheet may contain a lot of imperceptible spaces. Such coincidental occurrence might be towards the start or end of the text (leading and trailing spaces). Since extra spaces return an incorrect word count, the TRIM function is used before computing the length of the string.

The steps to count the total number of words in a cell of Excel are listed as follows:

Select the cell in the Excel sheet where you want the result to appear.
For counting the number of words in cell A1, enter the formula shown in the following image.
Click Enter and the exact number of words appear in cell B1.

Key Takeaways

The formula to count words of a particular range is “=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1.”
The word count formula is combined with the SUM or SUMPRODUCT function to handle arrays.
The SUBSTITUTE function replaces all the spaces of the cell with a vacant content string (").
The LEN function restores the length of the string without spaces.
The TRIM function removes the leading and trailing spaces found at the beginning or at the end of the text.
The number of words in a cell is equivalent to the number of spaces plus 1.

How to Count the Total Number of Words in a Range of Cells?

To count the number of words in a range of cells, apply the equation that counts the words in a cell and implant it either inside the SUM or the SUMPRODUCT function.

The formula to count words of a particular range is “=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1.”

Step 1: Select the range of data whose words you wish to count.

Step 2: Enter the formula in the cell where you want the result to display as shown in the succeeding image.

Step 3: Click "Enter" and the result appears in cell B1.

Step 4: Drag the fill handle to all cells to get the word count of each cell in Excel.

How to Count Specific Words in a Range?

To count the number of times a specific word appears in a range of cells, we utilize a comparative methodology. We count the explicit words in a cell and consolidate it with the SUM or SUMPRODUCT function.

Step 1: Select the cell and enter the formula “=(LEN(cell)-LEN(SUBSTITUTE(cell,word,"")))/LEN(word)” as shown in the following image.

Step 2: Click "Enter" to see the word count in cell A14. The formula we used above helps us know the number of times the word "February" is present in cell A2.

The result in cell A14 is 4.