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)
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top