Parsing comma/semicolon delimited line

[John Yazou]

Hi guys,
Am sure you guys can figure this out..

I have a string, which the # of comma delimited items varies..

eg. strLineBuffer = "A, B, C, D"

Is there some kind of function in excel vba that takes the string, and takes each word delimited with a comma and puts it in an array.

eg. MagicFunc (strLineBuffer)

returns an array that contains "A", "B", "C", "D"

Thanks...

[Maslan]

found this code in one of my workbooks.. it should suit your needs; somebody from tech dept programmed it, i think...

Function Parse(strBody As String) As Variant

Dim strTemp As String
Dim strTempRpt As String
Dim intCntr As Integer
Dim arrRptNames() As String
On Error Resume Next

strTemp = strBody

Do
' Increase array size to handle each found report - cumulative.
ReDim Preserve arrRptNames(intCntr)
strTempRpt = strTemp

If InStr(strTempRpt, ",") > 0 Then

arrRptNames(intCntr) = Trim(Left(strTempRpt, _
InStr(strTempRpt, ",") - 1))
Else
arrRptNames(intCntr) = Trim(strTempRpt)
Parse = arrRptNames()
Exit Function
End If
intCntr = intCntr + 1
strTemp = Mid(strTemp, InStr(strTemp, ",") + 1)
Loop Until InStr(strTemp, ",") = 0
ReDim Preserve arrRptNames(intCntr)

Parse = arrRptNames()
End Function

so Parse("A, B, C, D") will return an array containing A, B, C, D...

Cheers,