VBA Split function

The Excel VBA Split function splits any string into an array of substrings separated by a given delimiter, returning a VBA Array containing the split substrings.

While the VBA Split function splits a string into substrings, the VBA Join function does the opposite by joining an array of strings into a single string.

VBA Split Syntax

The syntax for the Split function in VBA is:

Split ( expression [,delimiter] [,limit] [,compare] )


The string to be split into separate substrings separated by the given delimiter.

Optional. The delimiter which separates the expression into separate substrings. By default this is a whitespace character (” “).

Optional. The limit of substrings to be returned by splitting the expression into separate substrings. By default this equals -1 which means all substrings are to be returned.

Optional. The type of comparison to perform when looking for the substring. This can be one of the following VBA Constants:

Constant Value Description
CompareMethod.Binary 0 Binary comparison (distinguishes letter case)
CompareMethod.Text 1 Textual comparison (ignores letter case)

Other Notes

The Split function will return a VBA Array containing the splitted substrings. The numbering of the first item in the VBA Array starts at 0.

Example usage

The VBA Split function can be used in VBA code. Let’s look at some VBA Split function examples:

'The default delimiter is a SPACE
Split "Hello World!"
'Result: "Hello", "World!"

'Splitting by the "." delimiter
Split "", "."
'Result: "1", "2", "3", "4", "5"