Word Count In Excel

I know you can create your own word counting function pretty quickly, like so:

1. Open the Visual Basic Editor, then add a module to your workbook.
2. In the module, add the following lines of code:

Function Word_Count(strText As String) As Long
Dim i, lngWords As Long

lngWords = 1

' Add one to the word count each time we encounter a space
For i = 1 To Len(strText)
If Mid(strText, i, 1) = " " Then
lngWords = lngWords + 1
End If
Next i

Word_Count = lngWords
End Function

That should give you a word count function for Excel. To use it, go back to Excel and type in some text. Then, in another cell, type in:

=Word_Count()

It should return the numberof words. Does that help?

The module only works on individual cells and not an array of cells. Also, it counts an empty cell as 1 - I assume because it is counting spaces.

Any further ideas?

Try this:

Function Word_Count(strText As String) As Long
Dim i, lngWords As Long

Word_Count = 0
lngWords = 1

' If it's empty, exit the function
If strText = "" Then Exit Function

' Add one to the word count each time we encounter a space
For i = 1 To Len(strText)
If Mid(strText, i, 1) = " " Then
lngWords = lngWords + 1
End If
Next i

Word_Count = lngWords
End Function

Function Word_Count_Range(rngRange As Range) As Long
Dim lngWords As Long
Dim rngCell As Range

lngWords = 0

' Call the Word_Count function for each cell in the range
For Each rngCell In rngRange
lngWords = lngWords + Word_Count(rngCell.Value)
Next

Word_Count_Range = lngWords
End Function