Don’t Waste Time Counting Words: Let Excel Formulas Do the Work

Have you ever wondered about word count in Excel? It’s a handy tool that can make your data analysis easier. Imagine the possibilities with this new skill. In this tutorial, I’ll show you how to find Word count in Excel using various formulas. Includes samples and a practice file.

I attended a workshop where the panelists provided their “best Excel tips”. One tip involved filtering a range of cells containing a certain number of words. A nice tip, but it left attendees wondering how to count words in Excel.

The summit tip was for a specific product, but opportunities exist where you might want to count words in an Excel cell. For example, I do a similar process when I pull a list of site search queries from my web analytics to see people’s interests.

Spaces Impact the Number of Words

The key to counting words in Excel is correctly identifying and counting the spaces between and around the words. You need to remove leading and trailing spaces in the cells, or the word count will be inflated. There are a couple of ways to do this. A simple way is to use an Excel add-on such as ASAP Utilities.

Another way is to use the Excel TRIM function. The trim function removes leading and trailing spaces in a cell. 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

In addition to the TRIM function, I’ll also use Excel’s LEN function and SUBSTITUTE functions. These are also considered TEXT functions.

LEN function returns the number of characters in a string. In my case, the number will reflect the number for each reference cell. Since a “space” is considered a character, it is counted.

SUBSTITUTE function 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,”firefox”,”google”) would replace the word “firefox” with “google” for cell reference 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”. All the single spaces have been removed, resulting in a string without spaces.

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.

To get the word count in cell A2 in my spreadsheet, I would use this formula in B2,

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1)

While stringing Excel functions together is efficient, the formula may be intimidating. You’ll note in the screen snap below and on the lesson spreadsheet that I’ve added more columns to clarify this.

Let me break this down for you.

1. 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.
2. 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.
3. We use LEN(SUBSTITUTE(A2)," ","") to remove the remaining spaces. We then count the characters in this new string.
4. We take the LEN count from Step 2 and subtract the LEN count from Step 3. We then add 1 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.

Presenting Word Count Another Way

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.

After writing this formula, I think I have a new appreciation for the ease at which some programs, like Microsoft Word, can return a word count.

Make a Custom Function to Count Words

After using this word count formula several times, I thought creating a custom function and assigning it to the Excel workbook would make more sense. This would save me from repeating these steps each time I needed to do an analysis.

You can add the code snippet below as a module in the VB editor. In my example, I called the function CellWordCount. That name is one I’m apt to remember, but you can choose a different name for your custom function.

Function CellWordCount(rng As Range) As Integer
    CellWordCount = UBound(Split(Application.WorksheetFunction.Trim(rng.Value), " "), 1) + 1
End Function

To use the function, click in an empty cell and type =CellWordCount and then your cell reference in parentheses.

Feel free to download the practice file and play around with it. Try editing some of the examples to include things like typos with consecutive extra space characters in between words. The sample workbook contains the formulas referenced here except for the custom function. Also, Microsoft has a page on how to create custom functions.

We can filter that column for a minimum or maximum value now that we have had Excel count words. Another option might be to convert the spreadsheet into a table and then add an Excel slicer.

Downloadable Resource

Hand-picked Excel Tutorials

• How to Sort Excel by Color