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

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

expressionThe 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 (default, case sensitive)
1 = vbTextCompare (not case sensitive)
2 = vbDatabaseCompare (uses an Access database)

* 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 given 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 the Microsoft documentation refer to docs.microsoft.com

Dim aValues As Variant 
Dim sStringConcat As String
sStringConcat = "A,B,C,D"
aValues = VBA.Split(sStringConcat, ",") '{"A","B","C","D"}
aValues = VBA.Split(sStringConcat, ",", -1) '{"A","B","C","D"}
aValues = VBA.Split(sStringConcat, ",", 4) '{"A","B","C","D"}
aValues = VBA.Split(sStringConcat, ",", 3) '{"A","B","C,D"}
aValues = VBA.Split(sStringConcat, ",", 2) '{"A","B,C,D"}
aValues = VBA.Split(sStringConcat, ",", 1) '{"A,B,C,D"}
aValues = VBA.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 = VBA.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 = VBA.Split("C:\Temp\SubFolder\FileName.txt", "\")
'aComponents '{"C:","Temp","SubFolder","FileName.txt"}
Debug.Print VBA.Join(aComponents)

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited Top