Pearson Software Consulting Services

    Word Count 

         Unlike MS Word, Excel doesn't have a built in method for counting the number of words in a spreadsheet. However, you can count the number of words with a simple VBA macro.



Sub CountWords()
    Dim WordCount As Long
    Dim Rng As Range
    Dim S As String
    Dim N As Long
    For Each Rng In ActiveSheet.UsedRange.Cells
        S = Application.WorksheetFunction.Trim(Rng.Text)
        N = 0
        If S <> vbNullString Then
            N = Len(S) - Len(Replace(S, " ", "")) + 1
        End If
        WordCount = WordCount + N
    Next Rng
    MsgBox "Words In ActiveSheet Sheet: " & Format(WordCount,"#,##0")
End Sub

  To count the words in a single cell, you can use the following formula:

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

This works by first using TRIM to convert multiple spaces to single spaces and remove any leading or trailing spaces. Then it subtracts from the Length of A1 the Length of  A1 after all spaces have been removed (SUBSTITUE).  The difference is the number of spaces in A1. Add 1 to that and you get the number of words. The same function in VBA is shown below. Note that it uses Excel's worksheet function TRIM, not VBA's Trim function. The two are not the same. Both functions remove leading and trailing spaces, but Excel's TRIM changes multiple spaces within the string to single spaces, while VBA's Trim function leaves multiple spaces within the string intact. For example,

Debug.Print Application.WorksheetFunction.Trim("   a   b   c   ")
a b c
Debug.Print Trim("   a   b   c   ")
a   b   c

Function WordsInCell(Cell As Range) As Variant
    If Cell.Cells.Count <> 1 Then
        ' return #VALUE if there is more
        ' than one cell referenced by Cell.
        WordsInCell = CVErr(xlErrValue)
        Exit Function
    End If
    If Len(Cell.Text) > 0 Then
        With Application.WorksheetFunction
            WordsInCell = Len(.Trim(Cell.Text)) - _
                Len(.Substitute(.Trim(Cell), " ", "")) + 1
        End With
        WordsInCell = 0
    End If
End Function



Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers

Copyright 1997-2007  Charles H. Pearson