SPLIT

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

Returns an array containing a specified number of substrings (Variant).

expression	The expression containing substrings and delimiters (String)
delimiter	(Optional) The character used to identify substring limits (String)
limit	(Optional) Number of substrings to be returned -1 = all substrings are returned.
compare	(Optional) A vbCompareMethod constant specifying the type of string comparison to use (Integer): -1 = vbUseCompareOption (uses the "Option Compare" setting) 0 = vbBinaryCompare (case sensitive) (default) 1 = vbTextCompare (not case sensitive) 2 = vbDatabaseCompare (uses an Access database)

REMARKS

* This function is case sensitive (by default).
* This function will always return a 0 based array.
* This function always returns a 1 dimensional array.
* This function ignores any Option Base 1 statements and always creates an array that starts at 0.
* If "expression" = "" (zero length string), then an empty array is returned.
* If "delimiter" is left blank, then the space character (" ") is used.
* If "delimiter" = "" (zero-length string), then a single-element array containing the "expression" is returned.
* If "limit" = -1, then the array will contain each substring as its own array item.
* If "limit" = 1, then the array will contain one item with all the substrings concatenated.
* If "limit" = 2, then the array will contain the first substring as one item and the second item with all the remaining substrings concatenated.
* If "limit" is left blank, then -1 is used and all the substrings are returned.
* If "compare" is left blank, then -1 is used. If there is no Option Compare statement provided then vbBinaryCompare (0) is used.
* If "compare" is Null, then an error occurs.
* When ever there is more than one space within the string, the corresponding array element is a zero length string.
* You can use the ARRAY function to return an array containing specific values.
* You can use the FILTER function to return an array containing a subset of a string array based on a filter condition.
* You can use the JOIN function to return a text string containing all the elements in an array.
* This function can return an empty array. When this happens the lowerbound is 0 and the upperbound is -1.
* The equivalent .NET function is Microsoft.VisualBasic.Strings.Split
* For more examples refer to the Splitting page.
* For the Microsoft documentation refer to learn.microsoft.com

Dim aValues As Variant 
Dim sStringConcat As String 
sStringConcat = "A,B,C,D" 
aValues = Split(sStringConcat, ",")       '{"A","B","C","D"}  
aValues = Split(sStringConcat, ",", -1)   '{"A","B","C","D"}  
aValues = Split(sStringConcat, ",", 4)    '{"A","B","C","D"}  
aValues = Split(sStringConcat, ",", 3)    '{"A","B","C,D"}  
aValues = Split(sStringConcat, ",", 2)    '{"A","B,C,D"}  
aValues = Split(sStringConcat, ",", 1)    '{"A,B,C,D"}  
aValues = Split(sStringConcat, ",", 0)    'empty  
'Debug.Print can only display text values, not entire arrays

'This will split the text string into individual characters
Dim aDigits As Variant 
aDigits = Split(VBA.StrConv("2468", 64), Chr(0))  '{"2","4","6","8",""}  
Debug.Print VBA.Join(aDigits) 

'This will split the fullpath into drive, folders and filename
Dim aComponents() As String 
aComponents = Split("C:\Temp\SubFolder\FileName.txt", "\") 
aComponents  '{"C:", "Temp", "SubFolder", "FileName.txt"}  
Debug.Print VBA.Join(aComponents)