Parse a string

  • Thread starter Thread starter Fahim
  • Start date Start date
F

Fahim

Hi,

Is there a way in vba to parse a comma delimited string?
For example mystring = "first,second,third"
I am trying to debug.print that should give
first
second
third

Appreciate any help, Thanks!
 
Try Split().

Works in A2002, 2003, and (I think) in A2000.
Results in a variant array of the values, so you can loop through from
LBound to UBound.
 
For example mystring = "first,second,third"
I am trying to debug.print that should give
first
second
third
Click to expand...

Apart from the obvious

debug.print replace(oldstring, ",", vbNewLine)

you can use the split() function


Hope that helps


Tim F
 
Hi Allen,

is there a way to do this in Access97?

I Know it's a little bit out of date...but here @ work I must use this old
version :-(

Thanks in advance
Michael
 
http://support.microsoft.com/?id=188007 has sample code that you can use in
Access 97 to give you the "new" string functions introduced in Access 2000.
It's not the greatest code (if you do a Google search, you should be able to
find much better code), but it does work.

Since that code was actually aimed at VB programmers, you'll need to change
all declarations

Optional bCompare As VbCompareMethod = vbBinaryCompare

to

Optional bCompare As Long = vbBinaryCompare
 
Hi,
Here is one way to do it. Place these functions in a standard module:

Public Function CountChar(strIn As String, CharToCount As String) As Integer
Dim i As Integer
Dim intPointer As Integer

intPointer = 1

Do While i <= Len(strIn)
If InStr(intPointer, strIn, CharToCount, vbTextCompare) <> 0 Then
i = i + 1
intPointer = InStr(intPointer, strIn, CharToCount, vbTextCompare) + 1
Else
Exit Do
End If
Loop

CountChar = i

End Function

Public Sub ParseString(expression As String, delimiter As String, arr() As String)

Dim charCount As Integer
Dim strTemp As String
Dim pos As Integer
Dim i As Integer


charCount = CountChar(expression, delimiter)
ReDim arr(0 To charCount)
strTemp = expression
Do
arr(i) = Left(strTemp, InStr(1, strTemp, delimiter, vbTextCompare) - 1)
strTemp = Mid(strTemp, InStr(1, strTemp, delimiter, vbTextCompare) + 1)
i = i + 1
Loop Until InStr(1, strTemp, delimiter, vbTextCompare) = 0
arr(i) = strTemp

End Sub


Then you can call it like so:
Dim myArr() As String
Dim i As Integer

ParseString "hi,there,you", ",", myArr()

For i = 0 To UBound(myArr)
MsgBox myArr(i)
Next

The MsgBox stuf was just for testing. The 1st argument to the ParseString function is the string
you want parsed, the 2nd is your delimiter and the 3rd is the array where the broken up string
will be placed.
 
Back
Top