Count Words in a Field

[novel]

Is it possible to use MS Access to count the number of words
in a data field?

[NeoPa]

Is it possible to use MS Access to count the number of words
in a data field?

I don't know of a way short of writing a function to process through the data character by character.

[southoz]

Good ay ,
try using the len function to get the length of the string then use a repeating instr function to find all the spaces in the string, that would give u a proper word count

southoz

[Andrew Thackray]

This function counts the number of words in text. Note it treats hyphenated words as two words. If you don't want this remove the hyphen case from the select function.

Function WordCount(ByRef Str As String) As Long

    Dim cnt As Long
    Dim Words As Long
    
    Words = 0
    WordCount = 0
    If Len(Str) = 0 Then
        Exit Function
    End If
    For cnt = 1 To Len(Str)
        Select Case Mid(Str, cnt, 1)
            Case " "
                Words = Words + 1
            Case "."
                Words = Words + 1
            Case ","
                Words = Words + 1
            Case ";"
                Words = Words + 1
            Case ":"
                Words = Words + 1
            Case "-"
                Words = Words + 1
        End Select
    Next cnt
    If Words = 0 Then Words = 1
    WordCount = Words
End Function

[Killer42]

Just a point - the Instr method, might be slightly more complex to code (though not difficult), but would likely execute faster (except possibly when dealing with very short strings).

Performance would only be an issue, of course, if you are driving it very hard - that is, hitting it for millions of strings, or something.

[Andrew Thackray]

The instr method would be quicker though more complex. however if the function has to deal with punctuation other than spaces betweeen words then it gets very much more complex and probably would not be much quicker.

[NeoPa]

Andrew,

Try the Case statement this way for brevity.

-Adrian.

        Select Case Mid(Str, cnt, 1)
            Case " ", ".", ",", ";", ":", "-"
                Words = Words + 1
        End Select

[Andrew Thackray]

Neat, I forgot I can use lists

[Killer42]

Andrew,
Try the Case statement this way for brevity.
-Adrian.

        Select Case Mid(Str, cnt, 1)
            Case " ", ".", ",", ";", ":", "-"
                Words = Words + 1
        End Select

Nice one. However, this will double count in many cases. A full stop, for example (a "period" to you Yanks) is usually going to be followed by at least one space. It might actually produce a more useful count if you don't look for all the punctuation, but just count gaps. For a really accurate count you might need a more complex routine.

The instr method would be quicker though more complex. however if the function has to deal with punctuation other than spaces betweeen words then it gets very much more complex and probably would not be much quicker.

Good point, I didn't really think about that. For just spaces, Instr is almost certainly, as discussed, a little more complex to code and a little faster to execute. For a more thorough search including commas, colons and so on, I would avoid the Instr method.

[Andrew Thackray]

Damn, I didn't think of the puctuation - space case. I'll have to amend the routine to fix that.

It strikes me that this is a useful function to have in my standard library so its worth the effort to get it right.

[NeoPa]

I wasn't going to mention it, but if it's an oft-used routine, you want to use a flag which remembers whether you're currently in word mode or between words mode. Hope that helps.
(It's good posting for you guys as you don't have to spell everything out in fine details :) )

[pks00]

What if u used the Split command?

Dim sWords() as String

sWords = Split(myfield," ")

msgbox "Number of words is " & ubound(sWords)

[Killer42]

What if u used the Split command?

Dim sWords() as String
sWords = Split(myfield," ")
msgbox "Number of words is " & ubound(sWords)

Nice shortcut, that - I like it!
I think the basic question that this thread hinges on is whether you want to take notice of punctuation or not. My feeling would be that to generate a word count you have two basic options.

• If you are only interested in gaps/spaces to delineate "words" (which would be my recommendation) , Split is probably your best bet.
• If you want to take all sorts of punctuation into account, you probably need to loop through character by character and code something like the Select Case.

[NeoPa]

Nice one pks00, and/but Killer is spot on too.

[Andrew Thackray]

Never knew there wuz a slpit function.

However nothing new under the sun. Looking up split on the net resulted in this set of handy routines, including wordcount.

http://j-walk.com/ss/excel/tips/tip93.htm