Contents
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
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.
Unlock Your Potential
Excel
Access
Power BI
