Hafiz Islam
Excel Doesn’t provide any built-in functions that can count words in a cell or a range of cells. However, by combining different functions you can create complex formulas to can perform almost any calculations. In this tutorial, you will see 2 handy examples to count words in Excel with formula.
Download Practice Workbook
You can download the following practice workbook that we have used to prepare this article.
2 Handy Examples to Count Words in Excel with Formula
Let’s introduce our sample dataset first. There are 2 columns, one contains Words, and another contains the value of word counts.
1. Combine LEN, TRIM, and SUBSTITUTE Functions to Count Words in a Cell
We use the SUBSTITUTE function to replace all spaces in the cell with a vacant string (“).
Then, the LEN function counts the length of the string without spaces.
Next, we minus the length of the string without spaces from the absolute length of the string. The number of total words in a cell is equal to the number of spaces plus 1. For this reason, we add 1 to the formula.
Finally, we use the TRIM function to remove extra spaces in a cell. A cell may include a lot of extra space. As extra spaces may return an incorrect word count, the TRIM function helps us to count words correctly.
Now, follow the steps below to count words in a cell of Excel.
📌 Steps:
- First, select a cell where you want to show the count number of words (In this example, C5). Type the following formula into the cell, then press ENTER.
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1
Counting Total Number of Words in a Range of Cells:
You can apply the previous formula to multiple cells to count the total number of words in a range of cells. Just follow the steps below to do this.
📌 Steps:
- Drag the Fill Handle to the remaining cells of the range.
- Here is the result that shows the word counts at each cell of the range B5:B14. Now, by applying the SUM function, we can easily get the total word counts. Into cell C15, type the following formula.
=SUM(C5:C14)
Read More: How to Count Repeated Words in Excel (11 Methods)
2. Formula to Count Specific Words in a Range in Excel
To count a specific word in a range, first, we count the word in a cell. Then, using the SUM function we can easily return the total number of words. To apply this, just follow the steps below.
📌 Steps:
- First, select the first cell of the Count column. Write down the following formula into the cell, then press ENTER. You will see the count number of “February” words at each cell of the range.
=(LEN(B5:B14)-LEN(SUBSTITUTE(B5:B14,February,"")))/LEN(February)
- Now, applying the SUM function to cell C15, you will get the total number of appearances of the February word in the range B5:B14.
=SUM(C5:C14)
Read More: Count Specific Words in a Column in Excel (2 Methods)
Conclusion
In this tutorial, I have discussed 2 handy examples to count words in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
