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

Returns the 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
2 = vbDatabaseCompare (uses an Access database)

* If "expression" = "" (zero length string), then an empty array is returned.
* If "delimiter" = "" (zero-length string), then a single-element array containing the "expression" is returned.
* If "delimiter" is left blank, then the space character (" ") is used.
* If "limit" is blank, then ??
* If "compare" is blank, then ??
* This function will always return a zero-based array irrespective of whether you declare "Option Base 1".
* The array returned is always a 1-dimensional array.
* When ever there is more than one space within the string, the corresponding array element is a zero length string.
* This function can return an empty array. When this happens the lowerbound is 0 and the upperbound is -1.
* You can use the JOIN function to return the text string containing the elements in an array.
* 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 = "one,two,three"
aValues = Split(sStringConcat, ",") = {"one","two","three"}

'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",""}

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