How to Get the Word Count in Excel

Want to get the word count in Excel? Believe it or not, Excel does not have an inbuilt word counter.

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.

This Tutorial Covers:

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:

1. There is a single space between each word
2. There are multiple spaces between words.

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:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

This would return 7 as the 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 of 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 mutliple 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:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

This is a similar formula used in the above example, with a slight change – we have also used theTRIM 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 Get Word Count 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 it’s easy to use.

Here is the code that will create this custom function to get the word count in Excel.

Function WordCount(CellRef As Range)
Dim TextStrng As String
Dim Result() As String
TextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"
Result = Split(TextStrng)
WordCount = UBound(Result()) + 1
End Function

Once created, you can use the WordCount function just like any other regular function.

Note that this function also handles leading, trailing and double spaces. In the above example, the word count is the same in all the three cases, and the formula used is also the same.

Where to Put this Code?

When creating a custom function, you need to put the code in the code window for the workbook in which you want the function.

Below are the steps to put the code for the ‘GetNumeric’ function in the workbook.

1. Go to Developer tab.
2. Click on Visual Basic option. This will open the VB editor in the backend.
3. In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. If you don’t see the Project Explorer go to the View tab and click on Project Explorer.
4. Go to Insert and click on Module. This will insert a module object for your workbook.
5. Copy and paste the code in the module window.

You May Also Like the Following Excel Tutorials:

• 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.