I recently attended a workshop with an expert’s panel where people provided their best Excel tips. One tip involved filtering cells with a certain number of words. A nice tip, but it left some attendees wondering how to count words in Excel. The program doesn’t have this feature, but you can find the answer by creating a formula to count words. (Includes sample Excel worksheet and formula)
The summit tip was for a specific product, but opportunities exist where you might want to count words in an Excel cell. I do a similar process when I pull a list of site search queries from my web analytics to see people’s interests.
Spaces and Counting Words
The key to counting words in Excel is to identify the spaces between words correctly. You need to remove leading and trailing spaces in the cells, or the count will be inflated. There are a couple of ways to do this. A simple way is to use Excel add-ons such as ASAP Utilities.
Another way is to use the Excel TRIM function. The trim function removes leading and trailing spaces in a cell. This text function also removes extra spaces between words to just one space. In the screen snap below, you can see that the spaces aren’t always obvious. You have to compare the counts in Columns B and C.
LEN returns the number of characters in a string. In my case, the number will reflect the number for each cell. Since a “space” is considered a character, it is counted.
SUBSTITUTE is similar to “search and replace” on a cell, except we can specify how many times the substitution should occur. For example, you could indicate once, all, or a specific number.
For example, the formula =SUBSTITUTE(A1,"example","sample") would replace the word “example” with “sample” for cell A1.
For our purposes, we want to substitute a space ” ” with nothing. Effectively, the function removes all spaces, so the words run together. “Example text” would change to “Exampletext.”
Understanding the Word Count Formula
One nice feature of Excel is that you can nest formulas that include multiple functions. The formula below references LEN, TRIM, and SUBSTITUTE. It also starts with the IF function, which we’ve outlined before.
Let me break this down for you.
We TRIM any extra spaces in cell A2 and determine if the cell is blank by using =IF(LEN(TRIM(A2))=0,0. If A2 is blank, it assigns the word count as 0.
If A2 isn’t blank, we count the characters in the cell using LEN(TRIM(A2)). You might think of this as our starting character count inclusive of spaces.
We use LEN(SUBSTITUTE(A2)," ","") to remove the remaining spaces. We then count the characters in this new string.
We take the LEN count from Step 2 and subtract the LEN count from Step 3. We then add one to the count to adjust for the first word.
In the above example, I placed the formula in Column B. However, you can also add more columns to show various stages. This often helps when learning a nested formula. In the screen snap below, my formula is in Column F.
If you prefer word problems, think of it this way. If the cell is empty, make the word count = 0. Otherwise, remove the extra spaces and count the characters in the cell. Hold that value as “A.” Now, remove all spaces in that cell and count the characters again. Hold that value as “B.” Your word count is (A-B) + 1.
“Sample example text” = LEN count of 19. This is your “A.”
“Sampleexampletext” = LEN count of 17. This is your “B.”
(19-17)+1 = word count of 3.
One Step Further
Downloadable Resource
attach_file Excel Word Count Lesson File
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-17