VB6Parse / Library / Arrays / split

Split Function

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

Syntax

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

Parameters

• expression (Required): String expression containing substrings and delimiters
• delimiter (Optional): String character used to identify substring limits
• If omitted, the space character (" ") is assumed
• limit (Optional): Number of substrings to be returned; -1 returns all substrings
• compare (Optional): Numeric value indicating comparison type (see Compare Settings)

Compare Settings

• vbBinaryCompare (0): Perform a binary comparison
• vbTextCompare (1): Perform a textual comparison
• vbDatabaseCompare (2): Perform a comparison based on information in your database

Return Value

• Returns a Variant containing a one-dimensional array of strings (zero-based)
• If expression is a zero-length string (""), returns an empty array
• If delimiter is a zero-length string or not found, returns a single-element array containing the entire expression

Remarks

The Split function breaks a string into substrings at the specified delimiter and returns them as an array. This is the opposite of the Join function, which combines array elements into a single string. - Returns a zero-based array (first element is index 0) - If expression is a zero-length string (""), Split returns an empty array - If delimiter is a zero-length string, a single-element array containing the entire expression is returned - If delimiter is not found, a single-element array containing the entire expression is returned - Delimiter characters are not included in the returned substrings - If limit is provided and is less than the number of substrings, the last element contains the remainder of the string (including delimiters) - Multiple consecutive delimiters create empty string elements in the array

Typical Uses

• Parse CSV Data: Split comma-separated values
• Extract Words: Split sentence into individual words
• Process Lines: Split multiline text into lines
• Parse Paths: Split file paths into components
• Extract Parameters: Parse parameter strings
• Data Import: Process delimited import files
• String Tokenization: Break strings into tokens
• Configuration Parsing: Parse config file entries

Common Errors

The Split function itself doesn't typically generate errors with valid inputs, but related operations can: - Error 13 (Type mismatch): If expression is not a string - Error 5 (Invalid procedure call): If limit is negative (other than -1) - Error 9 (Subscript out of range): When accessing array elements beyond bounds

Always validate inputs and array bounds:

On Error Resume Next
Dim parts() As String
parts = Split(text, ",")
If Err.Number <> 0 Then
    MsgBox "Error splitting text: " & Err.Description
End If

Performance Considerations

• Split is very efficient for moderate-sized strings
• For very large strings (>1MB), consider processing in chunks
• Avoid repeated Split calls in tight loops if possible
• Consider caching Split results if reused multiple times
• For complex parsing, Split may be slower than manual parsing

Best Practices

• Check Array Bounds: Always verify UBound before accessing elements
• Handle Empty Results: Check if array has elements before processing
• Trim Whitespace: Use Trim on results to remove unwanted spaces
• Validate Delimiter: Ensure delimiter is appropriate for data
• Use Limit: Limit number of splits when only need first few elements
• Handle Edge Cases: Test with empty strings, missing delimiters
• Consider Alternatives: For complex parsing, use dedicated parser
• Document Expected Format: Comment the expected delimited format
• Filter Empty Elements: Remove empty strings when caused by multiple delimiters
• Combine with Join: Use Join to reconstruct modified arrays

Comparison with Related Functions

Platform Considerations

• Available in VB6, VBA (Office 2000+)
• Not available in VBA prior to Office 2000
• Returns Variant array (can assign to String array)
• Zero-based array (unlike many VB arrays which are 1-based)
• Consistent behavior across platforms

Limitations

• Returns zero-based array (may be unexpected in VB6)
• Delimiter must be exact match (no regex)
• Single delimiter only (can't split on multiple different delimiters)
• No built-in trim of results
• Empty elements included when multiple consecutive delimiters present
• No built-in handling of quoted fields (CSV with commas in quotes)
• Maximum array size limited by memory

Related Functions

• Join: Combines array elements into a string with delimiter
• Filter: Returns a subset of array based on filter criteria
• InStr: Finds position of substring (useful before Split)
• Replace: Replaces occurrences of substring

Basic Examples

Example 1: Split Comma-Separated Values

Dim text As String
Dim parts() As String
text = "apple,banana,orange"
parts = Split(text, ",")
' parts(0) = "apple"
' parts(1) = "banana"
' parts(2) = "orange"

Example 2: Split Sentence Into Words (Default Space Delimiter)

Dim sentence As String
Dim words() As String
sentence = "The quick brown fox"
words = Split(sentence)
' words(0) = "The"
' words(1) = "quick"
' words(2) = "brown"
' words(3) = "fox"

Example 3: Split With Limit

Dim data As String
Dim items() As String
data = "one,two,three,four,five"
items = Split(data, ",", 3)
' items(0) = "one"
' items(1) = "two"
' items(2) = "three,four,five" (remainder)

Example 4: Split Multiline Text

Dim text As String
Dim lines() As String
text = "Line 1" & vbCrLf & "Line 2" & vbCrLf & "Line 3"
lines = Split(text, vbCrLf)
' lines(0) = "Line 1"
' lines(1) = "Line 2"
' lines(2) = "Line 3"

Common Patterns

Pattern 1: Parse a CSV line handling quotes

Function ParseCSVLine(line As String) As String()
    ' Simple CSV parsing (doesn't handle quotes)
    ParseCSVLine = Split(line, ",")
End Function

Pattern 2: Extract Words From Text, Handling Multiple Spaces

Function GetWords(text As String) As String()
    Dim words() As String
    Dim result() As String
    Dim i As Integer
    Dim count As Integer
    words = Split(Trim(text), " ")
    ' Filter out empty strings from multiple spaces
    count = 0
    For i = LBound(words) To UBound(words)
        If Len(words(i)) > 0 Then
            count = count + 1
        End If
    Next i
    ReDim result(0 To count - 1)
    count = 0
    For i = LBound(words) To UBound(words)
        If Len(words(i)) > 0 Then
            result(count) = words(i)
            count = count + 1
        End If
    Next i
    GetWords = result
End Function

Pattern 3: Split File Path Into Components

Function SplitPath(filePath As String) As String()
    Dim delimiter As String
    ' Handle both Windows and Unix paths
    If InStr(filePath, "\") > 0 Then
        delimiter = "\"
    Else
        delimiter = "/"
    End If
    SplitPath = Split(filePath, delimiter)
End Function

Pattern 4: Parse Key=Value Pairs

Sub ParseKeyValue(kvPair As String, key As String, value As String)
    Dim parts() As String
    parts = Split(kvPair, "=", 2)
    If UBound(parts) >= 0 Then
        key = Trim(parts(0))
        If UBound(parts) >= 1 Then
            value = Trim(parts(1))
        Else
            value = ""
        End If
    End If
End Sub

Pattern 5: Split Text Into Lines, Handling Different Line Endings

Function SplitLines(text As String) As String()
    Dim normalized As String
    ' Normalize line endings to vbCrLf
    normalized = Replace(text, vbCr & vbLf, vbLf)
    normalized = Replace(normalized, vbCr, vbLf)
    SplitLines = Split(normalized, vbLf)
End Function

Pattern 6: Parse Delimited Data With Custom Delimiter

Function ParseDelimitedData(data As String, delimiter As String) As Variant
    Dim lines() As String
    Dim result() As Variant
    Dim i As Integer
    lines = Split(data, vbCrLf)
    ReDim result(0 To UBound(lines))
    For i = LBound(lines) To UBound(lines)
        result(i) = Split(lines(i), delimiter)
    Next i
    ParseDelimitedData = result
End Function

Pattern 7: Extract Specific Fields From Delimited String

Function ExtractField(delimitedString As String, _
                      delimiter As String, _
                      fieldIndex As Integer) As String
    Dim fields() As String
    fields = Split(delimitedString, delimiter)
    If fieldIndex >= LBound(fields) And fieldIndex <= UBound(fields) Then
        ExtractField = fields(fieldIndex)
    Else
        ExtractField = ""
    End If
End Function

Pattern 8: Count Number Of Tokens In String

Function CountTokens(text As String, delimiter As String) As Integer
    Dim tokens() As String
    tokens = Split(text, delimiter)
    CountTokens = UBound(tokens) - LBound(tokens) + 1
End Function

Pattern 9: Split And Reverse The Order

Function ReverseSplit(text As String, delimiter As String) As String()
    Dim parts() As String
    Dim result() As String
    Dim i As Integer
    Dim count As Integer
    parts = Split(text, delimiter)
    count = UBound(parts) - LBound(parts)
    ReDim result(0 To count)
    For i = 0 To count
        result(i) = parts(count - i)
    Next i
    ReverseSplit = result
End Function

Pattern 10: Split And Remove Empty Elements

Function SplitNonEmpty(text As String, delimiter As String) As String()
    Dim parts() As String
    Dim result() As String
    Dim i As Integer
    Dim count As Integer
    parts = Split(text, delimiter)
    ' Count non-empty elements
    count = 0
    For i = LBound(parts) To UBound(parts)
        If Len(parts(i)) > 0 Then count = count + 1
    Next i
    If count = 0 Then
        ReDim result(0 To -1)  ' Empty array
    Else
        ReDim result(0 To count - 1)
        count = 0
        For i = LBound(parts) To UBound(parts)
            If Len(parts(i)) > 0 Then
                result(count) = parts(i)
                count = count + 1
            End If
        Next i
    End If
    SplitNonEmpty = result
End Function

Advanced Usage

Example 1: Parse CSV Data With Split

' Class: CSVParser
Private m_data() As Variant
Private m_rowCount As Integer
Private m_columnCount As Integer
Public Sub LoadCSV(csvText As String, Optional hasHeader As Boolean = True)
    Dim lines() As String
    Dim i As Integer
    Dim startRow As Integer
    ' Split into lines
    lines = Split(csvText, vbCrLf)
    If hasHeader Then
        startRow = 1
        m_rowCount = UBound(lines) - LBound(lines)
    Else
        startRow = 0
        m_rowCount = UBound(lines) - LBound(lines) + 1
    End If
    ' Get column count from first data row
    If UBound(lines) >= startRow Then
        Dim firstRow() As String
        firstRow = Split(lines(startRow), ",")
        m_columnCount = UBound(firstRow) - LBound(firstRow) + 1
    End If
    ' Parse data
    ReDim m_data(1 To m_rowCount, 1 To m_columnCount)
    For i = startRow To UBound(lines)
        Dim fields() As String
        Dim j As Integer
        fields = Split(lines(i), ",")
        For j = LBound(fields) To UBound(fields)
            If j - LBound(fields) + 1 <= m_columnCount Then
                m_data(i - startRow + 1, j - LBound(fields) + 1) = fields(j)
            End If
        Next j
    Next i
End Sub
Public Function GetValue(row As Integer, col As Integer) As String
    If row >= 1 And row <= m_rowCount And _
       col >= 1 And col <= m_columnCount Then
        GetValue = m_data(row, col)
    Else
        GetValue = ""
    End If
End Function
Public Property Get RowCount() As Integer
    RowCount = m_rowCount
End Property
Public Property Get ColumnCount() As Integer
    ColumnCount = m_columnCount
End Property
Public Function GetRow(row As Integer) As Variant
    Dim result() As String
    Dim i As Integer
    If row >= 1 And row <= m_rowCount Then
        ReDim result(1 To m_columnCount)
        For i = 1 To m_columnCount
            result(i) = m_data(row, i)
        Next i
        GetRow = result
    End If
End Function

Example 2: Parse Configuration Files

' Module: ConfigFileParser
Private m_settings As Object  ' Scripting.Dictionary
Public Sub LoadConfig(configText As String)
    Dim lines() As String
    Dim i As Integer
    Set m_settings = CreateObject("Scripting.Dictionary")
    m_settings.CompareMode = vbTextCompare
    lines = Split(configText, vbCrLf)
    For i = LBound(lines) To UBound(lines)
        Dim line As String
        line = Trim(lines(i))
        ' Skip empty lines and comments
        If Len(line) > 0 And Left(line, 1) <> "#" And Left(line, 1) <> ";" Then
            Dim parts() As String
            parts = Split(line, "=", 2)
            If UBound(parts) >= 1 Then
                Dim key As String
                Dim value As String
                key = Trim(parts(0))
                value = Trim(parts(1))
                m_settings(key) = value
            End If
        End If
    Next i
End Sub
Public Function GetSetting(key As String, Optional defaultValue As String = "") As String
    If m_settings.Exists(key) Then
        GetSetting = m_settings(key)
    Else
        GetSetting = defaultValue
    End If
End Function
Public Function GetSettingAsInteger(key As String, Optional defaultValue As Integer = 0) As Integer
    If m_settings.Exists(key) Then
        If IsNumeric(m_settings(key)) Then
            GetSettingAsInteger = CInt(m_settings(key))
        Else
            GetSettingAsInteger = defaultValue
        End If
    Else
        GetSettingAsInteger = defaultValue
    End If
End Function
Public Function GetSettingList(key As String, delimiter As String) As String()
    If m_settings.Exists(key) Then
        GetSettingList = Split(m_settings(key), delimiter)
    Else
        Dim empty() As String
        ReDim empty(0 To -1)
        GetSettingList = empty
    End If
End Function

Example 3: Process Text With Various Split Operations

' Class: TextProcessor
Public Function GetParagraphs(text As String) As String()
    ' Split by double line breaks
    Dim normalized As String
    normalized = Replace(text, vbCrLf & vbCrLf, vbLf & vbLf)
    normalized = Replace(normalized, vbCr, vbLf)
    GetParagraphs = Split(normalized, vbLf & vbLf)
End Function
Public Function GetSentences(text As String) As String()
    Dim temp As String
    Dim i As Integer
    ' Simple sentence splitting (doesn't handle abbreviations)
    temp = Replace(text, ". ", ".|")
    temp = Replace(temp, "! ", "!|")
    temp = Replace(temp, "? ", "?|")
    GetSentences = Split(temp, "|")
End Function
Public Function GetWords(text As String) As String()
    Dim cleaned As String
    Dim i As Integer
    cleaned = text
    ' Remove punctuation
    cleaned = Replace(cleaned, ".", " ")
    cleaned = Replace(cleaned, ",", " ")
    cleaned = Replace(cleaned, "!", " ")
    cleaned = Replace(cleaned, "?", " ")
    cleaned = Replace(cleaned, ";", " ")
    cleaned = Replace(cleaned, ":", " ")
    GetWords = Split(Trim(cleaned), " ")
End Function
Public Function CountWords(text As String) As Integer
    Dim words() As String
    Dim count As Integer
    Dim i As Integer
    words = GetWords(text)
    count = 0
    For i = LBound(words) To UBound(words)
        If Len(Trim(words(i))) > 0 Then
            count = count + 1
        End If
    Next i
    CountWords = count
End Function
Public Function GetUniqueWords(text As String) As String()
    Dim words() As String
    Dim dict As Object
    Dim i As Integer
    Dim result() As String
    Dim count As Integer
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    words = GetWords(text)
    For i = LBound(words) To UBound(words)
        Dim word As String
        word = Trim(words(i))
        If Len(word) > 0 Then
            dict(word) = True
        End If
    Next i
    ReDim result(0 To dict.Count - 1)
    Dim keys As Variant
    keys = dict.keys
    For i = 0 To dict.Count - 1
        result(i) = keys(i)
    Next i
    GetUniqueWords = result
End Function

Example 4: Import Delimited Data Files

' Module: DataImporter
Public Function ImportDelimitedFile(filePath As String, _
                                    delimiter As String, _
                                    Optional hasHeader As Boolean = True) As Variant
    Dim fileNum As Integer
    Dim fileContent As String
    Dim lines() As String
    Dim result() As Variant
    Dim i As Integer
    Dim startRow As Integer
    ' Read file
    fileNum = FreeFile
    Open filePath For Input As #fileNum
    fileContent = Input(LOF(fileNum), #fileNum)
    Close #fileNum
    ' Split into lines
    lines = Split(fileContent, vbCrLf)
    If hasHeader Then
        startRow = 1
    Else
        startRow = 0
    End If
    ' Parse each line
    ReDim result(startRow To UBound(lines))
    For i = startRow To UBound(lines)
        result(i) = Split(lines(i), delimiter)
    Next i
    ImportDelimitedFile = result
End Function
Public Function GetColumnFromData(data As Variant, columnIndex As Integer) As String()
    Dim result() As String
    Dim i As Integer
    Dim rowCount As Integer
    rowCount = UBound(data) - LBound(data) + 1
    ReDim result(0 To rowCount - 1)
    For i = LBound(data) To UBound(data)
        Dim row() As String
        row = data(i)
        If columnIndex >= LBound(row) And columnIndex <= UBound(row) Then
            result(i - LBound(data)) = row(columnIndex)
        Else
            result(i - LBound(data)) = ""
        End If
    Next i
    GetColumnFromData = result
End Function
Public Function FilterRows(data As Variant, columnIndex As Integer, _
                           filterValue As String) As Variant
    Dim result() As Variant
    Dim count As Integer
    Dim i As Integer
    ' Count matching rows
    count = 0
    For i = LBound(data) To UBound(data)
        Dim row() As String
        row = data(i)
        If columnIndex >= LBound(row) And columnIndex <= UBound(row) Then
            If row(columnIndex) = filterValue Then
                count = count + 1
            End If
        End If
    Next i
    ' Build result
    ReDim result(0 To count - 1)
    count = 0
    For i = LBound(data) To UBound(data)
        row = data(i)
        If columnIndex >= LBound(row) And columnIndex <= UBound(row) Then
            If row(columnIndex) = filterValue Then
                result(count) = row
                count = count + 1
            End If
        End If
    Next i
    FilterRows = result
End Function