Count the number of words in a given cell.

[ThrottleWorks]

I have downloaded & modified following code from http://www.cpearson.com/excel/wordcount.htm


but the code is giving me #Value! error.

Can someone please help me rectifying my error.


I have value in cell a5, the value is abc def efg.

I want to calculate no of words in cell a5.


Function CountWords()


Dim WordCount As Long

Dim Range("a4") As Range

Dim S As String

Dim N As Long

For Each Rng In Range

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 Function

 

[Hui]

Why not just use a formula:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

or

=LEN(TRIM(C3))-LEN(SUBSTITUTE(TRIM(C3)," ",""))+1

the 2nd formula takes care of leading or trailing spaces

 

[Hui]

or try this if you really want to use VBA

[pre]

Function CountWords(MyRng As Range)
Dim rng As Range
Dim WordCount As Long
Dim S As String
Dim N As Long
For Each rng In MyRng
S = Application.WorksheetFunction.Trim(rng.Text)
N = 0
If S <> vbNullString Then N = Len(S) - Len(Replace(S, " ", "")) + 1
WordCount = WordCount + N
Next rng
MsgBox "Words In ActiveSheet Sheet: " & Format(WordCount, "#,##0")
CountWords = WordCount
End Function

[/pre]

 

[ThrottleWorks]

The problem is I can not use formula, it has to be done with VBA only.


Your code is great help for me. This is working.


Thanks a lot fo the help.


Have a nice weekend.

 

[Marc L]

Maybe a bit late but this is my function (works only for one cell range) :

[pre]

Function NumWords(Rg As Range)
NumWords = UBound(Split(Rg)) + 1
End Function

[/pre]

Another thinking, another way, another code …

 

[Marc L]

Merge of Hui's code and mine's :

[pre]

Function CellWords(ByVal Rg As Range)   ' for only one cell
CellWords = UBound(Split(WorksheetFunction.Trim(Rg))) + 1
End Function

Function CountWords(Rg As Range)        ' for a range of cells
For Each Cel In Rg
CountWords = CountWords + CellWords(Cel)
Next
End Function

Sub Test()
Debug.Print CellWords(ActiveCell), CountWords(Selection)
End Sub

[/pre]

 

[Hui]

@Marc L

Split is a new function to me

Thanx

Hui...