In VBA, Split returns a Variant containing a zero-based, one-dimensional array of strings. It creates one array element for each piece of the input text separated by the delimiter you specify, or by a space if you omit the delimiter.
What the return value contains
Think of Split as turning one string into an ordered list of smaller strings. For example, splitting "red,green,blue" at each comma produces three elements: "red", "green", and "blue". The returned array starts at index 0, so those elements are at positions 0, 1, and 2.
The result is not a collection object, nor is it a worksheet range. It is an array held inside a Variant variable. That distinction matters when you declare variables and when you pass the result to another procedure. A common and flexible declaration is Dim parts As Variant, followed by assigning the result to parts.
The array contains text values, even when a piece looks like a number or date. For example, splitting "12|34" returns the strings "12" and "34"; VBA does not automatically turn those pieces into numeric values. Convert them explicitly if later calculations require numbers.
A basic VBA example
This example splits a comma-separated string, prints each element in the Immediate window, and shows how to use the zero-based indexes. In the VBA editor, open the Immediate window with Ctrl+G on Windows. On Mac, use the VBA editor’s View > Immediate Window command if the window is not already visible.
Sub ShowSplitParts()
Dim sourceText As String
Dim parts As Variant
Dim i As Long
' Split the text at each comma.
sourceText = "red,green,blue"
parts = Split(sourceText, ",")
' Print each piece, starting at index zero.
For i = LBound(parts) To UBound(parts)
Debug.Print i, parts(i)
Next i
End Sub
The output has indexes 0, 1, and 2. Using LBound and UBound makes the loop adaptable if the array bounds are not known in advance. For a normal nonempty result from Split, the lower bound is 0. This snippet only displays values; for any macro that changes a workbook, save a copy first because macro actions cannot be undone with Ctrl+Z.
Syntax and optional arguments
The function’s general form is Split(expression, [delimiter], [limit], [compare]). The expression is the string to divide. The other arguments are optional, but understanding them helps avoid surprises:
delimiteris the text that marks each division. If omitted, VBA uses a single space.limitsets the maximum number of pieces returned. The default is -1, which means return all pieces.comparecontrols how VBA matches the delimiter. You can use constants such asvbBinaryCompareandvbTextCompare.
A delimiter can be more than one character. If the delimiter is "--", VBA looks for that exact two-character sequence rather than treating each hyphen as a separate delimiter. Matching is based on the delimiter as a whole. This is useful for structured text that uses a consistent marker, such as a double colon or a pipe followed by a space.
The comparison setting applies to matching the delimiter, not to changing the text returned in the pieces. With vbTextCompare, a delimiter match is case-insensitive; with vbBinaryCompare, it is case-sensitive. If you omit compare, VBA follows the module’s Option Compare setting. In a module without an Option Compare Text statement, the usual default is binary comparison.
How delimiters, limits, and empty pieces work
Split preserves empty pieces created by adjacent or edge delimiters. For example, splitting "A,,B" at commas returns three elements: "A", an empty string, and "B". The empty item is meaningful: it tells you that there was nothing between the two commas. Similarly, a delimiter at the beginning or end of a string can create an empty first or last element.
That behavior is useful when positions in the input matter, but it can be troublesome if you expected VBA to discard blank fields. If the input is "A,,B", do not assume the result has only two elements. Check each element for an empty string if blank fields should be ignored, or use a separate filtering step.
The limit argument caps the number of returned pieces. For example, with source text "red,green,blue", delimiter ",", and a limit of 2, the result is "red" and "green,blue". The remaining text stays together in the final piece. This can help when only the first separator has structural meaning, such as separating a key from the rest of a line.
Two special cases are worth distinguishing. If the expression is a zero-length string, Split returns an empty array with no elements. If the delimiter is a zero-length string, it returns a one-element array containing the original expression. Also, if the delimiter does not occur in a nonempty expression, the result is a one-element array containing the whole expression.
Reading and using the returned array
Because the result is a zero-based array, a three-part result is accessed as parts(0), parts(1), and parts(2). Avoid assuming that every input produces a particular number of parts. A missing delimiter, an empty input, or a different input format can change the result. Check the input and the array bounds before accessing a position that might not exist.
For example, this procedure extracts the first token only when the input is not empty. It uses a Variant for the array and checks the source before indexing, avoiding an attempt to read an element from the empty array that Split("") returns.
Sub PrintFirstToken()
Dim sourceText As String
Dim parts As Variant
' Set this to the text you want to examine.
sourceText = "North region"
If Len(sourceText) > 0 Then
parts = Split(sourceText, " ")
Debug.Print parts(0)
Else
Debug.Print "No text to split"
End If
End Sub
For a full loop, use LBound(parts) and UBound(parts) after confirming that the result contains elements. If the original string might be empty, handle that case first rather than relying on an upper-bound check against an empty array. As with other VBA macros, save a copy of the workbook before running code that makes changes; Ctrl+Z does not reverse macro actions.
Common uses and practical limitations
Split is handy when text follows a simple, predictable delimiter pattern. Typical cases include separating a first and last name at a space, breaking a line into fields at a tab, or reading a short list separated by semicolons. It can also make a string easier to validate by letting code inspect each piece individually.
Here is a small example that separates a tab-delimited line and reports the number of fields. The example does not modify cells or attempt to parse quoted data.
Sub CountTabFields()
Dim lineText As String
Dim fields As Variant
' Example line with three tab-separated values.
lineText = "A104" & vbTab & "Widget" & vbTab & "18"
fields = Split(lineText, vbTab)
Debug.Print "Field count: "; UBound(fields) - LBound(fields) + 1
End Sub
Simple splitting is not the same as parsing a formal data format. For example, a comma inside a quoted CSV field is not necessarily a field separator. Splitting a CSV row at every comma can therefore break values such as "Smith, Jordan" into unintended pieces. For full CSV files, use a parser that understands quoting and escaped quotes rather than relying on a plain delimiter split.
Likewise, splitting a date at slashes does not validate that the result is a real date, and splitting a number does not establish that its pieces are valid numeric values. Split performs text division; validation, conversion, and error handling are separate tasks.
Split compared with related tools
In VBA, Split turns a string into an array. The related Join function works in the opposite direction: it combines array elements into one string, placing a delimiter between them. A simple round trip can reconstruct the original text when the pieces and delimiter are handled consistently, although empty fields and data containing the delimiter can make reconstruction ambiguous.
Excel’s worksheet function TEXTSPLIT is related in purpose but is not the same function. In Microsoft 365 and Excel 2024, TEXTSPLIT can return separated values into worksheet cells as a dynamic array. VBA’s Split returns an array to VBA code; it does not spill values into cells by itself. To place the VBA results on a sheet, code must assign them to a range or write elements to cells.
For a one-time manual operation in Excel, Data > Text to Columns may be more convenient than writing VBA. For repeated automation, or when the text needs additional processing, VBA’s Split can be appropriate. Choose based on where the result needs to go and whether the input follows a simple delimiter rule.
Frequently Asked Questions
Does VBA Split return a string or an array?
It returns a Variant containing a one-dimensional array of strings. Store it in a Variant variable, then access its elements with parentheses, such as parts(0).
Is the first Split element indexed at 0 or 1?
It is indexed at 0. The array returned by Split is zero-based, regardless of the module’s Option Base setting. Use LBound and UBound when looping over a nonempty result.
What does Split return when the delimiter is missing?
If the input is nonempty and the delimiter does not appear, the function returns a one-element array containing the complete input string. It does not return an error just because no match was found.
Does Split remove empty values between delimiters?
No. Consecutive delimiters produce an empty-string element between them, and a delimiter at an edge can produce an empty edge element. Check for zero-length pieces if your processing should skip them.
Can Split use a delimiter with multiple characters?
Yes. The delimiter can be a multi-character string, and VBA searches for that sequence as the separator. It does not automatically split on each character within that delimiter.
Key takeaway
The practical answer is that VBA Split returns a zero-based array of string pieces inside a Variant. Its delimiter, comparison setting, and limit determine where and how much the text is divided, while empty inputs and repeated delimiters affect the number of elements. Once those rules are clear, the returned array is straightforward to inspect and process safely.
Related
- C++ Split String Into Array: Practical Methods
- JavaScript split(): Syntax, Examples, and Common Mistakes
- Chemistry Balance Equation Worksheet: Practice and Answers
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Overflow Error: Causes, Examples, and FixesAn Excel VBA overflow error is Run-time error 6: VBA tried to store or calculate a number that…
- Excel VBA Array Duplicate Values: Find, Keep, or RemoveYes. To find duplicate values in an Excel VBA array, scan its elements and track values already seen;…
- VBA Range Is Nothing? Meaning, Checks, and Common FixesIn Excel VBA, a Range is Nothing when an object variable does not currently refer to a range…
- VBA Modify Pivot Tables in Excel: Examples and TipsYes. VBA can modify an Excel PivotTable by moving fields between rows, columns, filters, and values; setting report…
- VBA Import CSV Files: Methods, Code, and Common IssuesTo import a CSV file with VBA, use Excel’s Workbooks.OpenText method to parse it with the correct delimiter…
- VBA Loop Types: For, Do, and While ExplainedVBA has four commonly encountered loop constructs: For…Next, For Each…Next, Do…Loop, and While…Wend. Use For…Next when a counter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.