count words VBA

[Dalia]

how to count the number of words in each cell in a range with VBA?

 

[shahin]

Put words in column A between 1 to 5 cell and get results adjacent to it.

Sub count_length()
Dim x As Long

For x = 1 To 5
    Cells(x, 2) = Len(Cells(x, 1))
Next x

End Sub

 

[Hui]

I would use a Function such as:

Function CountWords(word) As Integer

CountWords = Len(word) - Len(Replace(word, " ", "")) + 1

End Function

then in Excel
=CountWords(A2)

or in VBA
NoWords = CountWords(word)