How to use the VBA SPLIT Function (Syntax + Example)

HomeVBA Functions LIST (Category Wise)How to use the VBA SPLIT Function (Syntax + Example)

The VBA SPLIT function is listed under the array category of VBA functions. When you use it in a VBA code, it splits the specified string into multiple sub-strings using a delimiter. In simple words, you can split a text into multiple values using a delimiter.

Syntax

Split(Expression, [Delimiter], [Limit], [Compare])

Arguments

  • Expression: The original text string which you want to split.
  • [Delimiter]: The delimiter that you want to use while splitting the Expression [This is an optional argument and if omitted VBA takes “ ” by default].
  • [Limit]: An integer to specify the maximum number of substrings to be returned [This is an optional argument and if omitted VBA takes -1 to return all substrings by default].
  • [Compare]: A string value to define the comparison to make while filtering array [This is an optional argument and if omitted VBA takes vbBinaryCompare by default].
    • vbBinaryCompare: For binary comparison.
    • vbTextCompare: For text comparison.
    • vbDatabaseCompare: For Database Comparison.

Example

To practically understand how to use VBA SPLIT function, you need to go through the below example where we have written a vba code by using it:

Sub example_SPLIT()
Range("B1").Value = Split(Range("A1"), "-")
End Sub

In the above code, we have used the SPLIT to split the string from the cell A1 using the “-” delimiter and it has returned the result in the cell B1.

Related Functions

ARRAY | FILTER | JOIN | LBOUND | UBOUND