Word Count in Excel

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 ")
prints
a b c
while
Debug.Print Trim(" a b c ")
prints
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
Else
WordsInCell = 0
End If
End Function