Word Count in Excel – How to Check the Number of Words

As an Excel expert, I often get asked about the best ways to count words in a spreadsheet. While Excel doesn‘t have a built-in word count feature like Microsoft Word, there are several methods you can use to quickly check the number of words in a cell or range of cells. In this guide, I‘ll walk you through some easy formulas as well as how to create your own custom word count function.

Why Count Words in Excel?

You might be wondering, why bother counting words in Excel? While it‘s not a common task, there are actually many scenarios where knowing the word count can be quite useful:

• Analyzing survey responses or customer feedback to get stats like average words per comment
• Checking if product descriptions, ad copy, or other text content meets a required word count
• Comparing the length of different text entries to identify outliers or inconsistencies
• Creating an index or table of contents with page/word counts for each section
• Proofreading and editing text to fit within word limits for forms, reports, etc.

How to Count Words in Excel with Formulas

The simplest way to count words in Excel is by using a formula that checks the number of spaces in a string of text. Here‘s a breakdown of the functions we‘ll be using:

The LEN Function

LEN is an Excel function that returns the number of characters in a text string. For example:

=LEN("Emily Smith") // Returns 10

The TRIM Function

TRIM removes any extra spaces at the beginning or end of a text string. This is important because leading or trailing spaces can throw off our word count. For example:

=TRIM(" Emily Smith ") // Returns "Emily Smith"

The SUBSTITUTE Function

SUBSTITUTE replaces existing text with new text. We can use this to remove all spaces from our original string. For example:

=SUBSTITUTE("Emily Smith"," ","") // Returns "EmilySmith"

Putting It All Together

To count the words in a cell, we just need to put the above functions together like this:

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

Here‘s how this formula works:

1. TRIM(A1) removes any leading/trailing spaces from the text in cell A1
2. LEN(TRIM(A1)) counts the total characters in the trimmed string
3. SUBSTITUTE(A1," ","") removes all spaces from the original text
4. LEN(SUBSTITUTE(A1," ","")) counts the number of characters excluding spaces
5. By subtracting the length without spaces from the total length, we get the number of spaces
6. Finally, we add 1 to get the number of words (since the number of words is always the number of spaces + 1)

For example, if A1 contains the text "Emily Smith", our formula would evaluate like this:

=LEN(TRIM("Emily Smith"))-LEN(SUBSTITUTE("Emily Smith"," ",""))+1
=LEN("Emily Smith")-LEN("EmilySmith")+1
=10-9+1
=2

So the total word count is 2. Let‘s look at a practical example.

Example: Counting Words in a Customer Review

Suppose we have a spreadsheet containing customer reviews like this:

Review
This product is amazing! It works great and was so easy to set up out of the box. Highly recommended.
I was a bit skeptical at first, but after using it for a few weeks I‘m really happy with my purchase.
Not impressed at all. Poor quality and terrible customer service. I‘m returning it for a refund ASAP!

To count the words in each review, we just need to enter our formula next to each one:

Review	Word Count
This product is amazing! It works great and was so easy to set up out of the box. Highly recommended.	=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1
I was a bit skeptical at first, but after using it for a few weeks I‘m really happy with my purchase.	=LEN(TRIM(A3))-LEN(SUBSTITUTE(A3," ",""))+1
Not impressed at all. Poor quality and terrible customer service. I‘m returning it for a refund ASAP!	=LEN(TRIM(A4))-LEN(SUBSTITUTE(A4," ",""))+1

The formulas will automatically calculate the number of words in each review:

Review	Word Count
This product is amazing! It works great and was so easy to set up out of the box. Highly recommended.	20
I was a bit skeptical at first, but after using it for a few weeks I‘m really happy with my purchase.	22
Not impressed at all. Poor quality and terrible customer service. I‘m returning it for a refund ASAP!	17

Creating a Custom Word Count Function with VBA

While the formula method works fine for counting words in a single cell, it‘s not very convenient if you need to count words across multiple cells or files on a regular basis. For more flexibility, we can create a custom function (also known as a User Defined Function or UDF) using Excel‘s built-in programming language called VBA.

Setting Up the Developer Tab

First, you‘ll need to enable the Developer tab on the Excel ribbon if it‘s not already visible:

1. Click File > Options > Customize Ribbon
2. Under Main Tabs, check the box next to Developer
3. Click OK

Launching the Visual Basic Editor

Next, open the Visual Basic Editor by clicking Developer > Visual Basic (or press ALT+F11).

In the Project Explorer pane, double-click ThisWorkbook under VBAProject. This will open up a code window where we can enter our function.

Creating the Word Count Function

Copy and paste the following code into the code window:

Function WordCount(text As String) As Long
    WordCount = Len(Trim(text)) - Len(Replace(text, " ", "")) + 1
End Function

Here‘s how this code works:

• We define a new function called WordCount that takes a single text argument and returns a long integer
• The logic is basically the same as the formula version, except we use Replace instead of Substitute (since Substitute isn‘t available in VBA) and store the final result in the WordCount variable, which gets returned to the calling cell

Press ALT+Q to close the Visual Basic Editor and return to Excel.

Using the Word Count Function

Now we can simply use =WordCount(A1) to count the number of words in cell A1. The real beauty is that we can apply this to multiple cells at once. For example, to count the total words across A1, A2 and A3, just enter:

=WordCount(A1)+WordCount(A2)+WordCount(A3)

Or to count words in a whole column, we can use a SUM formula like:

=SUM(WordCount(A1:A100))

Installing the Function as an Excel Add-In

To make our custom word count function available in other workbooks, we need to save it as an Excel Add-In:

1. In the Visual Basic Editor, click File > Export File…
2. Change Save As Type to Excel Add-In (*.xlam)
3. Choose a file name and location and click Save
4. In Excel, click File > Options > Add-ins
5. Next to Manage Excel Add-ins click Go…
6. Click Browse and locate the add-in file you just saved
7. Make sure the box next to your add-in is checked, then click OK

The WordCount function will now appear in the list of available functions whenever you start typing a formula in any workbook.

Handling Special Cases

Our custom function handles most typical situations, but there are a few special cases to be aware of:

• Blank cells will return a word count of 1 (since "" is treated as a single "word"). You can add an IF statement to your function to handle blanks if needed.

• Cells containing only numbers or symbols with no spaces will also return 1. Again, you can modify the function to exclude these if necessary.

• If a cell contains more than 255 characters, you‘ll get a #VALUE! error when using the formula method. However, our custom function can handle text strings up to about 2 billion characters.

Alternative Methods for Counting Words

In addition to formulas and VBA, there are a couple other ways to get word counts in Excel:

Using the Word Object Library

If you have Microsoft Word installed, you can use the Word object model to count words in Excel:

1. In the Visual Basic Editor, click Tools > References…
2. Scroll down and check the box next to Microsoft Word [version] Object Library
3. Click OK

Then use code like this to count words:

Sub WordCount()
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Set wdDoc = wdApp.Documents.Add
    wdDoc.Content.Text = ActiveCell.Value
    MsgBox wdDoc.ComputeStatistics(wdStatisticWords)
    wdDoc.Close False
    wdApp.Quit
    Set wdDoc = Nothing
    Set wdApp = Nothing
End Sub

Using Third-Party Tools

There are also various free and paid add-ins that can add word count functionality to Excel, such as:

• ASAP Utilities
• Kutools for Excel
• Professor Excel Tools

However, these may be overkill if word counting is all you need.

Conclusion

To recap, we‘ve covered several ways to check word counts in Excel:

1. Using a formula with the LEN, TRIM and SUBSTITUTE functions
2. Creating a custom VBA function
3. Saving a custom function as an add-in
4. Using the Word object model
5. Installing third-party add-ins

For most users, I recommend starting with the formula method to see if it meets your needs. If you work with a lot of text data in Excel, creating a custom function is a good way to streamline the process. Just be aware of the potential limitations when dealing with blank cells, cells with over 255 characters, etc.

I hope this guide has been a helpful introduction to word counting in Excel. Let me know if you have any other questions!