VBA Use of Split to create an array from a string


Split Function

returns a zero-based, one dimensional array containing a specified number of substrings.


Split(expression [, delimiter [, limit [, compare]]])

expressionRequired. String expression containing substrings and delimiters. If expression is a zero-length string("" or vbNullString), Split returns an empty array containing no elements and no data. In this case, the returned array will have a LBound of 0 and a UBound of -1.
delimiterOptional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
limitOptional. Number of substrings to be returned; -1 indicates that all substrings are returned.
compareOptional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.


The compare argument can have the following values:

Description-1Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare0Performs a binary comparison.
vbTextCompare1Performs a textual comparison.
vbDatabaseCompare2Microsoft Access only. Performs a comparison based on information in your database.


In this example it is demonstrated how Split works by showing several styles. The comments will show the result set for each of the different performed Split options. Finally it is demonstrated how to loop over the returned string array.

Sub Test
    Dim textArray() as String

    textArray = Split("Tech on the Net")
    'Result: {"Tech", "on", "the", "Net"}

    textArray = Split("", ".")
    'Result: {"172", "23", "56", "4"}

    textArray = Split("A;B;C;D", ";")
    'Result: {"A", "B", "C", "D"}

    textArray = Split("A;B;C;D", ";", 1)
    'Result: {"A;B;C;D"}

    textArray = Split("A;B;C;D", ";", 2)
    'Result: {"A", "B;C;D"}

    textArray = Split("A;B;C;D", ";", 3)
    'Result: {"A", "B", "C;D"}

    textArray = Split("A;B;C;D", ";", 4)
    'Result: {"A", "B", "C", "D"}

    'You can iterate over the created array
    Dim counter As Long

    For counter = LBound(textArray) To UBound(textArray)
        Debug.Print textArray(counter)
 End Sub