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] )
Parameters
expression
The string to be split into separate substrings separated by the given delimiter.
delimiter
Optional. The delimiter which separates the expression into separate substrings. By default this is a whitespace character (” “).
limit
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.
compare
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 "1.2.3.4.5", "." 'Result: "1", "2", "3", "4", "5"
