How to Parse a Delimited String Using InStr and Mid

Last reviewed: June 21, 1995
Article ID: Q126939
The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 2.0 and 3.0

SUMMARY

Visual Basic programmers are sometimes presented with data in a delimited string. This article shows by example how to use the InStr and Mid functions to find and extract the parts of a string between delimiters.

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.


Additional reference words: 2.00 3.00
KBCategory: kbprg kbcode
KBSubcategory: PrgOther


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.