VBA Tutorial => Use Of Split To Create An Array From A String

Example

Split Function

returns a zero-based, one dimensional array containing a specified number of substrings.

Syntax

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

Part	Description
expression	Required. String expression containing substrings and delimiters. If expression is a zero-length string("" or vbNullString), Split returns an empty array containing no elements and no data. In this case, the returned array will have a LBound of 0 and a UBound of -1.
delimiter	Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
limit	Optional. Number of substrings to be returned; -1 indicates that all substrings are returned.
compare	Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

Settings

The compare argument can have the following values:

Constant	Value	Description
Description	-1	Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare	0	Performs a binary comparison.
vbTextCompare	1	Performs a textual comparison.
vbDatabaseCompare	2	Microsoft Access only. Performs a comparison based on information in your database.

Example

In this example it is demonstrated how Split works by showing several styles. The comments will show the result set for each of the different performed Split options. Finally it is demonstrated how to loop over the returned string array.

Sub Test Dim textArray() as String textArray = Split("Tech on the Net") 'Result: {"Tech", "on", "the", "Net"} textArray = Split("172.23.56.4", ".") 'Result: {"172", "23", "56", "4"} textArray = Split("A;B;C;D", ";") 'Result: {"A", "B", "C", "D"} textArray = Split("A;B;C;D", ";", 1) 'Result: {"A;B;C;D"} textArray = Split("A;B;C;D", ";", 2) 'Result: {"A", "B;C;D"} textArray = Split("A;B;C;D", ";", 3) 'Result: {"A", "B", "C;D"} textArray = Split("A;B;C;D", ";", 4) 'Result: {"A", "B", "C", "D"} 'You can iterate over the created array Dim counter As Long For counter = LBound(textArray) To UBound(textArray) Debug.Print textArray(counter) Next End Sub

Got any VBA Question?

Ask any VBA Questions and Get Instant Answers from ChatGPT AI: ChatGPT answer me! PDF - Download VBA for free Previous Next