Function CountCSWords(ByVal s) As Integer
     'Counts the words in a string that are separated by commas.

Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
     CountCSWords = 0
     Exit Function
End If
WC = 1
Pos = InStr(s, ",") '분리하고자하는 문자를 넣는다
Do While Pos > 0
     WC = WC + 1
     Pos = InStr(Pos + 1, s, ",")
Loop

CountCSWords = WC
End Function

Function GetCSWord(ByVal s, Indx As Integer)
 'Returns the nth word in a specific field.
Dim WC As Integer, Count As Integer
Dim SPos As Integer, EPos As Integer

WC = CountCSWords(s)
If Indx < 1 Or Indx > WC Then
     GetCSWord = Null
     Exit Function
End If

Count = 1
SPos = 1
For Count = 2 To Indx
     SPos = InStr(SPos, s, ",") + 1
Next Count

EPos = InStr(SPos, s, ",") - 1
If EPos <= 0 Then EPos = Len(s)
     GetCSWord = Trim(Mid(s, SPos, EPos - SPos + 1))
End Function


Sub Test()

Dim strAString As String
Dim I As Integer
Dim intCnt As Integer

strAString = "This,calls,the,two,functions,listed,above"

 'Find out how many comma separated words
 'are present
intCnt = CountCSWords(strAString)


 'Now call the other function to retrieve each one in turn
For I = 1 To intCnt
Debug.Print GetCSWord(strAString, I)
Next
End Sub

Copyright By AccessVision