# How to Count Words in Excel with Formula (2 Handy Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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)` ### 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)` ## Related Articles #### Hafiz Islam

Hi there. I am Hafiz, graduated from BUET. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. Now you can see my articles in the ExcelDemy blog.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  