Ever wished you could quickly count the words in an Excel cell? Mastering this skill can make your data analysis more productive. In this tutorial designed for beginners, I’ll guide you through how to count words in Excel using straightforward formulas. Learn to identify and handle extra spaces and even build a custom word count function for repeated use. Includes practice file.
Knowledge You’ll Gain:
- Understand why accurate word counts are crucial for data analysis in Excel.
- Learn how to use formulas like LEN, TRIM, and SUBSTITUTE to count words accurately.
- Discover how to manage extra spaces that can impact word counts.
- Create a custom word count function for efficiency.
- Improve your ability to filter and analyze text-based data in Excel.
I recently attended a workshop where one of the ‘best Excel tips’ involved filtering data based on word count. While this sounded useful, it left many attendees, including myself, wondering how to actually count words in Excel. Accurate word counts are essential for a variety of tasks, such as analyzing survey responses or filtering product descriptions based on their length. Without accurate counts, you risk drawing incorrect conclusions from your data.
How to Handle Extra Spaces
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. Otherwise, your 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 which offers countless helpful tools.
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 numbers 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.
The 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.
The SUBSTITUTE function is similar to “search and replace” on a cell, except we can specify how often 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.
Excel 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 picture below and on the lesson spreadsheet that I’ve added more columns to clarify this.
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 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.
Word Count Formula Explained
If you prefer word problems, think of the formula this way. If the cell is empty, make the word count = 0. Otherwise, remove the extra spaces and count the characters in the cell. Store that value as “A.” Now, remove all spaces in that cell and count the characters again. Store 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.
Create a Custom Function (VBA)
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.
- Press F11 to open Excel’s Visual Basic Editor.
- From the Insert tab, select Module.
- In the editor, copy and paste the code block below.
Function CellWordCount(rng As Range) As Integer
CellWordCount = UBound(Split(Application.WorksheetFunction.Trim(rng.Value), " "), 1) + 1
End Function
- Press Ctrl + S to save the custom function.
- To use the function, click an empty cell and type =CellWordCount and then your cell reference in parentheses.
In the code, we use a function called Split to divide the text into separate words, treating each space as a separator. Then, we use a function called UBound to find the position of the last word in this list of separated words. Since the list starts counting from zero, we add 1 to get the total number of words.
By now, you should know how to count words in Excel using built-in formulas and the custom function above. We’ve explored why accurate word counts matter and how to handle extra spaces. We even created a reusable VBA function to streamline the process.
To test your knowledge, why not download the practice file and experiment with the examples? Try adding some typos or extra spaces and see how the formulas handle them. The sample workbook contains all the formulas we discussed, except for the custom VBA function. You can learn more about creating custom functions on Microsoft’s support website.
Once you’re comfortable with counting words, you can unlock even more possibilities. For example, you can filter your data based on word count to find specific entries. You can also convert your data into an Excel Table, which offers advanced features like filtering and sorting. Excel Tables can be further enhanced with slicers, which provide interactive filtering options.