VBA - Split Function
A Split Function returns an array that contains a specific number of values split based on a delimiter.
Syntax
Split(expression[,delimiter[,count[,compare]]])
Parameter Description
Expression − A required parameter. The string expression that can contain strings with delimiters.
Delimiter − An optional parameter. The parameter, which is used to convert into arrays based on a delimiter.
Count − An optional parameter. The number of substrings to be returned, and if specified as -1, then all the substrings are returned.
Compare − An optional parameter. This parameter specifies which comparison method is to be used.
0 = vbBinaryCompare - Performs a binary comparison
1 = vbTextCompare - Performs a textual comparison
Example
Add a button and add the following function.
Private Sub Constant_demo_Click() ' Splitting based on delimiter comma '$' Dim a as Variant Dim b as Variant a = Split("Red $ Blue $ Yellow","$") b = ubound(a) For i = 0 to b msgbox("The value of array in " & i & " is :" & a(i)) Next End Sub
When you execute the above function, it produces the following output.
The value of array in 0 is :Red The value of array in 1 is : Blue The value of array in 2 is : Yellow