Example
Split Function
returns a zero-based, one dimensional array containing a specified number of substrings.
Syntax
Split(expression [, delimiter [, limit [, compare]]])
| Part | Description |
|---|---|
| expression | Required. 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. |
| delimiter | Optional. 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. |
| limit | Optional. Number of substrings to be returned; -1 indicates that all substrings are returned. |
| compare | Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values. |
Settings
The compare argument can have the following values:
| Constant | Value | Description |
|---|---|---|
| Description | -1 | Performs a comparison using the setting of the Option Compare statement. |
| vbBinaryCompare | 0 | Performs a binary comparison. |
| vbTextCompare | 1 | Performs a textual comparison. |
| vbDatabaseCompare | 2 | Microsoft Access only. Performs a comparison based on information in your database. |
Example
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("172.23.56.4", ".")
'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)
Next
End Sub
