VBA Function To Calculate Number of Words in a String
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.
Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Get answers right away at our AE Excel Support Forums!
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