Do
Do While Mid$(sSource, pointer, 1) = SP 'skip consecutive spaces
pointer = pointer + 1
Loop
If x = n Then 'the target word-number
lEnd = InStr(pointer, sSource, SP) 'pos of space at end of word
If lEnd = 0 Then lEnd = Len(sSource) + 1 ' or if its the last word
Pword = Mid$(sSource, pointer, lEnd - pointer)
Exit Do 'word found, done
End If
pos = InStr(pointer, sSource, SP) 'find next space
If pos = 0 Then Exit Do 'word not found
x = x + 1 'increment word counter
pointer = pos + 1 'start of next word
Loop
End Function
Public Function Words(ByVal sSource As String) As Long
'=================================================
' Words returns the number of words in a string
' Usage:
' Words("red blue green") 3
'=================================================
Const SP As String = " "
Dim lSource As Long 'length of sSource
Dim pointer As Long 'start parameter of Instr()
Dim pos As Long 'position of target in InStr()
Dim x As Long 'word count
sSource = CSpace(sSource)
lSource = Len(sSource)
If lSource = 0 Then Exit Function
'count words
x = 1
pointer = 1
Do
Do While Mid$(sSource, pointer, 1) = SP 'skip consecutive spaces
pointer = pointer + 1
Loop
pos = InStr(pointer, sSource, SP) 'find next space
If pos = 0 Then Exit Do 'no more words
x = x + 1 'increment word counter
