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.

Basic syntax


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.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *