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:
Where the function arguments are:
|Expression||-||The text string that you want to split.|
|[Delimiter]||-||[Delimiter] is set to be a space " ".If omitted, the|
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.
This can have any of the following values:
If the supplied Expression is an empty string, the split function returns an empty array.
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' The array "names" now has length 3, and contains the values
names = Split( "John Paul Smith" )
' "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.
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' The array "substrings" now has length 4, and contains the values
substrings = Split( "C:\Users\My Documents\File.txt", "\" )
' "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"