But don’t worry. A cool bunch of excel functions (or a little bit of VBA if you’re feeling fancy) can easily do this for you. In this tutorial, I will show a couple of ways to count words in Excel using simple formulas. And at the end, will also cover a technique to create a custom formula using VBA that will quickly give you the word count of any text in any cell.
Formula to Get Word Count in Excel
Before I give you the exact formula, let’s quickly cover the logic to get the word count. Suppose I have a sentence as shown below for which I want to get the word count.
While Excel cannot count the number of words, it can count the number of spaces in a sentence. So to get the word count, we can count these spaces instead of words and add 1 to the total (as the number of space would be one less the number of words). Now there can be two possibilities: So let’s see how to count the total number of words in each case.
Example 1 – When there is a single space between words
Let’s say I have the following text in cell A1: Let the cat out of the bag
To count the number of words, here is the formula I would use: This would return ‘7’ as a result. Here is how this formula works:
LEN(A1) – This part of the formula returns 26, which is the total number of characters in the text in cell A1. It includes the text characters as well as the space characters. SUBSTITUTE(A1,” “,””) – This part of the formula removes all the spaces from the text. So the result, in this case, would be Letthecatoutofthebag. LEN(SUBSTITUTE(A1,” “,“”) – This part of the formula counts the total number of characters in the text that has no spaces. So the result of this would be 20. LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”)) – This would subtract the text length without spaces from the text length with spaces. In the above example, it would be 26-20 which is 6. =LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”))+1 – We add 1 to the overall result as the total number of spaces is one less than the total number of words. For example, there is one space in two words and two spaces in three words.
Now, this works well if you have only one space character between words. But it wouldn’t work if you have more than one space in between words. In that case, use the formula in the next example.
Example 2: When there are multiple spaces between words
Let’s say you have the following text: Let the cat out of the bag
In this case, there are multiple space characters between words. To get the word count, we first need to remove all the extra spaces (such that there is only one space character between two words) and then count the total number of spaces. Here is the formula that will give us the right number of words: This is a similar formula used in the above example, with a slight change – we have also used the TRIM function here. Excel TRIM function removes any leading, trailing, and extra spaces (except single spaces between words). The rest of the formula works the same (as explained in Example 1).
Note: If there are no spaces between words, it is considered as one word.
Using VBA Custom Function to Count Words in Excel
While the above formulas work great, if you have a need to calculate the word count often, you can use VBA to create a custom function (also called a User Defined Function). The benefit of using a custom function is that you can create it once and then use it like any other regular Excel function. So instead of creating a long complex formula as we did in the two examples above, you have a simple formula that takes the cell reference and instantly gives you the word count. Here is the code that will create this custom function to get the word count in Excel. Once created, you can use the WordCount function just like any other regular Excel function.
In the above code for the custom function, I have used the worksheet TRIM function to remove any leading, trailing, and double spaces in between words. This ensures that all the three cells give the same result, as only the words are counted and not the double spaces. How this formula works: The above VBA code first uses the TRIM function to remove all the leading, trailing and double spaces from the text string in the referenced cell. Once it has the cleaned string, it uses the SPLIT function in VBA to split the text string based on the delimiter, which we have specified to be the space character. So each word is separated as stored as a separate item in the Result variable. We then use the UBOUND function to count the total number of items that got stored in the Result variables. Since VBA has a base of 0, we need to add 1 to get the total number of words. This means that Result(0) stores the first word, Result(1) stores the second word, and so on. Since this counting starts from 0, we need to add 1 to get the real word count. Where to Put this Code? When creating a custom function, you need to put the code in the VB Editor of the workbook (which is the back end of the workbook where you can write code to automate tasks and create custom functions). Below are the steps to put the code for the ‘GetNumeric’ function in the workbook. Once you have copied the code in the code window, you can go back to your worksheet and use this function just like any other regular Excel function. Just type =Word and it will show you the formula in the list.
It takes one argument, which is the cell reference and instantly gives you the word count in it.
How to Count Cells that Contain Text Strings. Using Multiple Criteria in Excel COUNTIF and COUNTIFS Function. How to Add Leading Zeroes in Excel. Remove Spaces in Excel – Leading, Trailing, and Double. How to Extract the First Word from a Text String in Excel
