The VBA Split Function

Related Function:

VBA Join

Description

The VBA Split function splits a string into a number of substrings and returns a one-dimensional array of substrings.

The syntax of the function is:

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

Where the function arguments are:

Expression

The text string that you want to split.

[Delimiter]

The delimiter that is to be used to specify where the supplied Expression should be split.

If omitted, the [Delimiter] is set to be a space " ".

[Limit]

An optional integer argument, specifying the maximum number of substrings to be returned.

If the [Limit] argument is omitted, it has the default value -1, denoting that all substrings should be returned.

[Compare]

An optional VbCompareMethod enumeration value, specifying the type of comparison that should be used for the substrings.

This can have any of the following values:

vbBinaryCompare	-	performs a binary comparison
vbTextCompare	-	performs a text comparison
vbDatabaseCompare	-	performs a database comparison

If omitted, the [Compare] argument uses the default value vbBinaryCompare.

If the supplied Expression is an empty string, the split function returns an empty array.

VBA Split Function Examples

Example 1

The following VBA code splits the string "John Paul Smith" into three substrings, using the space character as a delimiter.

' Split the string "John Paul Smith" into substrings.

								Dim names() As String

								names = Split( "John Paul Smith" )

' The array "names" now has length 3, and contains the values
' "John", "Paul" and "Smith"

The above call to the VBA Split function returns three substrings in the form of a one-dimensional array, which is then stored in the array "names".

Therefore, following the call to the Split function, the array "names" contains the following three elements:

names(0) = "John"
names(1) = "Paul"
names(2) = "Smith"

Note that, in the above example, the [Delimiter] argument has been omitted from the function and so by default, the space is used as the delimiter.

Example 2

The following VBA code splits the string "C:\Users\My Documents\File.txt" into four substrings, using the string "\" as a delimiter.

' Split the string "C:\Users\My Documents\File.txt" into substrings.

								Dim substrings() As String

								substrings = Split( "C:\Users\My Documents\File.txt", "\" )

' The array "substrings" now has length 4, and contains the values
' "C:", "Users", "My Documents" and "File.txt"

The above call to the VBA Split function returns four substrings in the form of a one-dimensional array, which is then assigned to the array "substrings".

The array "substrings" then has the following elements:

substrings(0) = "C:"
substrings(1) = "Users"
substrings(2) = "My Documents"
substrings(3) = "File.txt"

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page