How to Tally Words in Excel (with Formulas & VBA)

Tally means to count the number of items. So tallying words simply means counting the words. Although there is no direct way to count words, you can combine Excel functions, VBA code, etc to count words easily.

Key Takeaways

To count words in Excel, follow the steps below:
➤ Enter the formula: =LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1
➤ Replace “cell” with the cell containing the words.
➤ Press enter to get the word count.
➤ To tally words in Excel, count the spaces between the words and add 1 to the result.

In this article, we’ll learn different ways to tally words in a cell and a range of cells using LEN, TRIM, SUBSTITUTE, TEXTSPLIT functions, VBA code, etc. We’ll also explore how to tally specific words.

In the following dataset, we have the product name in Column A, the country of origin in Column B, and the product description in Column C. We will tally the words in the product description cell by combining the LEN, TRIM, and SUBSTITUTE functions. This is handy for a quick tally of the words in a cell.

➤ Select the output cell (D2) and type the following formula:

Or, If you’re using Excel 365, then use this formula to tally words:

The previous formula will return 1 for empty cells. So we can use an IF statement to check for blank cells.

➤ Use this formula to handle empty cells

Or, the updated formula for newer versions will be:

To tally the words in a range of cells we can wrap the previous formula with the SUM or the SUMPRODUCT function. This will give the total count of the words in the selected cell range.

➤ Select the output cell (B8) and type the formula. For Excel 2019 or earlier versions, press the Ctrl + Shift + Enter keys. On Excel 365 or later versions just press Enter.

Or, alternatively, go to the cell (B8) and type the formula:

To tally the number of times a certain word or text has appeared in a cell we can use the LEN and SUBSTITUTE functions. For example: I want to tally the occurrence of the word “and” in cell C2.

➤ Select the cell D2 and enter the formula:

If you want to count both the lowercase and uppercase of the word then use the LOWER and UPPER functions respectively.

➤ Select the cell D2 and enter the formula:

For tallying specific words in a range of cells we can use a similar approach i.e. placing the formula inside the SUM or the SUMPRODUCT functions.

➤ Select cell B8 and type the formula. Press Ctrl + Shift + Enter to apply the array formula.

You can use a simple VBA macro to make a custom function for tallying words. This helps you avoid complex formulas and you can use it like other Excel functions.
Steps:
➤ Go to the Developer tab and click the Visual Basic option.

➤ Click on Insert and select Module.

➤ Copy the VBA code and paste it into the Module window.

Function TallyWords(rng As Range) As Long
    Dim cell As Range
    Dim wordCount As Long
    Dim words As Variant
    
    For Each cell In rng
        words = Split(Trim(cell.Value), " ")
        wordCount = wordCount + UBound(words) + 1
    Next cell
    
    TallyWords = wordCount
End Function

➤ Go back to your Excel sheet and type the custom function name in cell B8.

The function requires only one argument - a cell reference or range of cells. It returns a count of the word.

Downloadable Resources

Get our practice workbook or necessary files that we have worked with to prepare this article.

Wrapping Up

In this tutorial, we've learned several ways to tally words in Excel. We looked at how to tally words in a single cell and how to handle empty cells. Then, we tallied words for a range of cells and also learned to tally specific words within a cell and across a range. Finally, we applied a simple VBA macro to create a word-counting function. Feel free to download the practice file and let us know which method you like the most.