Get Number of Words in a Cell Excel VBA code example

Number Of words

Since Excel offers no direct function by which you can get number of words in a cell or string. I have created a small function which you can copy/paste in your excel file and call in the workbook to get the count.

Code example

Public Function WORDCOUNT(oRange As Range) As Long
On erro GoTo errh
    WORDCOUNT = 0
    Const SPACE As String = " "
    Dim oCell As Range
    Set oCell = oRange.Cells(1, 1)
    
    If oCell.Value = "" Or Len(oCell.Value) = 0 Then
        Exit Function
    End If
    
    Dim objArray
    objArray = Split(oCell.Value, SPACE)
    Dim obj
    For Each obj In objArray
        WORDCOUNT = WORDCOUNT + 1
    Next obj
    
    'Cleanup
    If Not oCell Is Nothing Then
        Set oCell = Nothing
    End If
errh:
    If Err.Number <> 0 Then
        Exit Function
    End If
End Function

Implementation

Output

Using Formula

Output

Next >> MID Function or MID Formula in Excel with example

Share on Facebook

Tweet