VBA - Split Function


Advertisements

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
vba_arrays.htm
Advertisements