How to Parse a Delimited String Using InStr and Mid

MORE INFORMATION

Programmers can use the InStr function to search through a string for a sub string, starting at a specified position. When parsing a delimited string, use the InStr function to find each occurance of the delimiter in the string. Do this in a loop using the position of a previously found delimiter as the starting point for each subsequent search with InStr.

When the positions of two delimiters within the string have been found, the Mid function can be used to extract the piece of the string between them.

The example program below provides a generic function that parses a string with a given delimiter and returns the pieces in a dynamic array.

Step-by-Step Example

1. Start a new project in Visual Basic. Form1 is created by default.
2. Put the following ParseString function in the General Declarations section of Form1:

      ' Enter the following Function statement as one, single line:
      Function ParseString (SubStrs() As String, ByVal SrcStr As String,
         Byval Delimiter As String) As Integer
   
         ' Dimension variables:
         ReDim SubStrs(0) As String
         Dim CurPos As Long
         Dim NextPos As Long
         Dim DelLen As Integer
         Dim nCount As Integer
         Dim TStr As String
   
         ' Add delimiters to start and end of string to make loop simpler:
         SrcStr = Delimiter & SrcStr & Delimiter
         ' Calculate the delimiter length only once:
         DelLen = Len(Delimiter)
         ' Initialize the count and position:
         nCount = 0
         CurPos = 1
         NextPos = InStr(CurPos + DelLen, SrcStr, Delimiter)
   
         ' Loop searching for delimiters:
         Do Until NextPos = 0
            ' Extract a sub-string:
            TStr = Mid$(SrcStr, CurPos + DelLen, NextPos - CurPos - DelLen)
            ' Increment the sub string counter:
            nCount = nCount + 1
            ' Add room for the new sub-string in the array:
            ReDim Preserve SubStrs(nCount) As String
            ' Put the sub-string in the array:
            SubStrs(nCount) = TStr
            ' Position to the last found delimiter:
            CurPos = NextPos
            ' Find the next delimiter:
            NextPos = InStr(CurPos + DelLen, SrcStr, Delimiter)
         Loop
   
         ' Return the number of sub-strings found:
         ParseString = nCount
   
      End Function
   
   						

3. Add a command button (Command1) to Form1 and put the following code in the Command1 Click event:

      Sub Command1_Click ()
         Dim DataString As String
         Redim SubStr(0) As String
         Dim SubStrCount As Integer
         Dim i As Integer
   
         ' Create a comma-delimited string:
         DataString = "One,Two,Three,Four"
         Print "Delimited String: " & DataString
   
         ' Parse the string into sub-strings:
         SubStrCount = ParseString(SubStr(), DataString, ",")
   
         ' Display the sub-strings:
         For i = 1 to SubStrCount
            Print "Sub-String " & i & ": " & SubStr(i)
         Next
      End Sub
   
   						

4. Save the Project and press F5 to run the application.

The delimited string should be displayed on the form as well as each sub-string extracted from it.

NOTE: The ParseString routine returns an empty string if it finds two consecutive delimiters.