How to use Split function in VBA?

In VBA (Visual Basic for Applications), the `Split` function is used to divide a string into an array of substrings based on a specified delimiter. This can be very useful for parsing comma-separated values (CSVs), tab-delimited data, or other kinds of structured text.

Here’s a basic example of how to use the `Split` function in VBA:

Sub SplitExample()
    Dim inputString As String
    Dim delimiter As String
    Dim stringArray() As String
    Dim i As Integer
    
    inputString = "apple,orange,banana,grape" ' Your string to be split
    delimiter = "," ' Define the delimiter
    
    ' Split the string using the defined delimiter
    stringArray = Split(inputString, delimiter)
    
    ' Iterate over the array and print each element
    For i = LBound(stringArray) To UBound(stringArray)
        Debug.Print stringArray(i)
    Next i
End Sub

When run, this code will print out each fruit in the `Immediate Window` of the VBA editor:

apple
orange
banana
grape

Explanation of the code:

`inputString` contains the string you want to split.
`delimiter` is the character that you want to split the string on. In this case, it’s a comma.
`Split(inputString, delimiter)` is the call to the `Split` function with `inputString` and `delimiter` as arguments. `Split` returns an array of strings.
`LBound(stringArray)` returns the lowest index of the array (usually 0), and `UBound(stringArray)` returns the highest index.
The `For` loop iterates over the array, and `Debug.Print` outputs each element in the `Immediate Window`.

You can change the `delimiter` to any character or string you want to use as a separator. Also, if you don’t specify a `delimiter`, then the space character is used as the default delimiter.

facebooklinkedin