What Does Split Return in VBA? Array Results Explained

VBA & macros · Updated November 22, 2026

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:

  • delimiter is the text that marks each division. If omitted, VBA uses a single space.
  • limit sets the maximum number of pieces returned. The default is -1, which means return all pieces.
  • compare controls how VBA matches the delimiter. You can use constants such as vbBinaryCompare and vbTextCompare.

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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.