counting

[Dave B]

Is there a better way to count the number of words in a string than:

intWords = 1
For i = 1 To Len(strIssuerName)
If Mid(strIssuerName, i, 1) = " " Then intWords = intWords + 1
Next i

? This obviously isn't perfect since if there were two spaces in a row it
would count an extra word...

Also, is there an easy way to pick off the last two words in a string? I'm
currently using ExtractElement from one of J Walkenbach's books.

Thanks.
Dave

 

[Chip Pearson]

Dave,

Try the following:

Sub CountWords()
Dim S As String
Dim V As Variant
Dim N As Long
S = " this is a test"
S = Application.Trim(S)
V = Split(S, " ")
N = UBound(V) - LBound(V) + 1
Debug.Print "there are " & N & " words"
End Sub

For the last and next to last words, use
LastWord = V(UBound(V))
NextToLastWord = V(UBound(V) - 1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com