VBA Function To Calculate Number of Words in a String

September 20th, 2008 | Categories: Strings | Tags: , , , ,

The following VBA function counts the number of words in a string:

Function Number_of_Words(Text_String As String) As Integer
'Function counts the number of words in a string
'by looking at each character and seeing whether it is a space or not
Number_of_Words = 0
Dim String_Length As Integer
Dim Current_Character As Integer

String_Length = Len(Text_String)

For Current_Character = 1 To String_Length

If (Mid(Text_String, Current_Character, 1)) = " " Then
    Number_of_Words = Number_of_Words + 1
End If

Next Current_Character
End Function

It is worth noting a couple of points:
• This code must be inserted in a Workbook Module – from the VBA pane, click on Insert and then module

• The function can be called from an excel workbook cell either with an either explicit text or cell reference as an argument:
A3 = Number_of_Words(“Pig Dog Cat”)
A4 = Number_of_Words(D1)

The function works by traversing the length of a string and seeing if the next character is a space and if so it adds one to the number of spaces in the string.

Leave a comment | Trackback
  1. Jon Peltier
    September 20th, 2008 at 13:01
    Reply | Quote | #1

    An alternative, especially if you want to do something with the words, is something like the function below, which returns the number of words and an array containing these words:

    Function CountWords(sInput As String, iWords As Long, vWords As Variant) As Boolean
    vWords = Split(sInput, ” “)
    iWords = UBound(vWords) + 1 – LBound(vWords)
    CountWords = True
    End Function

    Sub TestCountWords()
    Dim sSentence As String
    Dim iCount As Long
    Dim vSplit As Variant
    Dim bTest As Boolean
    sSentence = InputBox(“Type a sentence”)
    bTest = CountWords(sSentence, iCount, vSplit)
    MsgBox sSentence & vbNewLine & vbNewLine & “has ” & iCount & ” words.”
    End Sub