How to Count Total Number of Words in a Cell or Range in Excel using VBA

Last updated: 26th February 2025

If you frequently use Excel, you've likely encountered the need to count the total number of words in a cell or range. In this article, we provide a simple and effective Excel VBA code to help you achieve this task. Enhance your spreadsheet skills and streamline your workflow with this straightforward method.

Count Total number of Words in a Cell without VBA (using helper columns)

Excel does not provide an in-built function to count words in cells. However, there are methods to achieve this. If you are not using VBA, you can use the following formula to find the total number of words in a cell.

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

The formula is applied to the string of values in cell A2. For instance, if cell A2 contains a string with various data, the formula will return a result of 6.

Count total number of words in a cell in Excel using a formula

LEN(A2): This function calculates the total number of characters in cell A2, including spaces.

SUBSTITUTE(A2, " ", ""): This function removes all the spaces in the text of cell A2. Essentially, it substitutes every space character with an empty string, so you're left with a continuous string of characters without spaces.

LEN(SUBSTITUTE(A2, " ", "")): This calculates the length of the text in cell A2 after all the spaces have been removed.

LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")): This subtracts the length of the text without spaces from the length of the original text. The result is the number of spaces in the original text.

+ 1: This is added to account for the first word. For instance, if there are 3 spaces, it means there are 4 words.

🚀 In a recent article, I discussed a similar issue involving in-built functions. Specifically, I demonstrated how to check if a cell or range contains numbers using the isNumeric() function in VBA, complete with a practical example.

Now, lets see how you can do this using a Macro.

Count Total number Words in a Cell using VBA

Add a button (an ActiveX control) on your worksheet and write the below code.

Option Explicit
Private Sub CommandButton1_Click()
    countWords (Worksheets("Sheet1").Range("A2:A10"))
End Sub

Sub countWords(objRange As Range)
    Dim cell As Variant
    Dim element As Variant
    Dim iCount As Integer
    Dim iCnt As Long
    Dim iRow As Long

    iRow = 2
    iCount = 1

    For Each cell In objRange      
        For iCnt = 1 To Len(cell.Value)  
            element = Mid(cell, 1, InStr(cell, " "))

            If Trim(element) <> "" Then
                iCount = iCount + 1
                objRange.Cells(iRow - 1, 2) = iCount
            End If

            cell = Replace(cell, element, "")
        Next iCnt
        
        iRow = iRow + 1
        iCount = 1          // re-assign the counter.
    Next cell
End Sub

The button's click event calls a procedure named countWords(). It takes a parameter in the form of a range. You can extend your range if you want.

Count total number of words in a cell or range in Excel using VBA

The procedure above runs two loops. The first loop gets a string value from each row. The second loop reads each element (words and other characters) in the string.

The Mid() function extracts a word from the string and add 1 to the counter (iCount). Finally, I am using the Replace() function to replace the extracted word from the string. The loop runs until it reaches the last word in the string.

← PreviousNext →