This, I think, is the  simplest of code to find the word count in a cell using the Split function. Its a single line code but very useful.

Download the attachment file to see how this is used as user defined function in excel and as a formula.

Function intWordCount(rng As Range) As Integer
 
    intWordCount = UBound(Split(rng.Value, " "), 1) + 1
 
End Function