VBA Split Function: Syntax, Examples, and Common Uses

VBA & macros · Updated November 22, 2026

In VBA, Split divides a text string wherever a chosen delimiter appears and returns the pieces as a zero-based, one-dimensional array. For example, Split("red,green,blue", ",") returns three elements: red, green, and blue.

What Split does

Split is a VBA function for breaking text into parts. It is useful when a string contains a predictable separator, such as commas between values, spaces between words, a slash between a folder path and a filename, or line breaks between lines. You can then inspect, transform, or process each part separately.

It is a programming-language function, not a worksheet formula. You use it in VBA code, such as a procedure in Excel’s Visual Basic Editor. Excel for the web does not run VBA macros; use desktop Excel for VBA. The function itself does not change cells or split a column automatically: your code decides what to do with the returned array.

Syntax and return value

The syntax is Split(expression, [delimiter], [limit], [compare]). The required expression is the string to divide. The other arguments are optional:

  • delimiter is the text that marks each boundary. If omitted, VBA uses a space.
  • limit sets the maximum number of substrings returned. The default, -1, means return all possible pieces.
  • compare specifies how VBA compares text while matching the delimiter. The default is binary comparison. Common choices are vbBinaryCompare and vbTextCompare.

The result is a Variant containing a one-dimensional string array. Its indexes start at zero, regardless of the module’s Option Base setting. If a string has three pieces, their indexes are 0, 1, and 2—not 1, 2, and 3. This is a frequent source of off-by-one errors when code is adapted from examples using other kinds of arrays.

How delimiters and limits work

A delimiter can be more than one character. For example, splitting on "::" treats the two-character sequence as one separator; it does not split separately at each colon. The separator is removed from the returned pieces. If separators occur next to each other, the empty text between them is preserved. Thus splitting "a,,c" on a comma gives "a", an empty string, and "c".

A positive limit caps the number of returned elements. Any remaining text is kept together in the final element. For instance, a limit of 2 when splitting "one,two,three" on commas yields "one" and "two,three". This can be useful when only the first field matters and the rest should remain intact.

Two empty-string 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. Code should not assume these cases behave like a normal split with one empty field.

A basic VBA example

This procedure splits a comma-separated string and prints each item in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows, then run the procedure. On a Mac, use the Visual Basic Editor’s View > Immediate Window command if the shortcut differs in your Excel setup.

Sub ShowSplitItems()
    Dim textLine As String
    Dim items As Variant
    Dim i As Long

    ' Split the sample text at each comma.
    textLine = "red,green,blue"
    items = Split(textLine, ",")

    For i = LBound(items) To UBound(items)
        Debug.Print i, items(i)
    Next i
End Sub

The output has indexes 0, 1, and 2 alongside the three colors. The code uses LBound and UBound rather than hard-coding those bounds, which is a sound habit when the number of pieces may change. Before running a macro in a workbook, save a copy: changes made by VBA cannot be undone with Ctrl+Z.

Working with the returned array

Because the result is an array, you can assign one element to a variable, loop through all elements, or use its bounds to determine how many pieces it contains. The following example extracts fields by position and reports how many were found:

Sub ReadDelimitedRecord()
    Dim recordText As String
    Dim fields As Variant
    Dim fieldCount As Long

    ' Split the sample record into fields.
    recordText = "Mira|North|Active"
    fields = Split(recordText, "|")
    fieldCount = UBound(fields) - LBound(fields) + 1

    Debug.Print fields(0)
    Debug.Print fields(1)
    Debug.Print fields(2)
    Debug.Print "Fields: "; fieldCount
End Sub

For this sample, the printed fields are Mira, North, and Active, and the count is 3. If the input might be empty, check its length before calling UBound, because an empty array has no upper bound to retrieve. For example, test Len(recordText) > 0 before treating the result as an ordinary array. Also consider whether a string containing only delimiters represents valid empty fields in your application; Split preserves those fields rather than deciding what they mean.

Useful parsing patterns

Many everyday VBA tasks involve simple, known separators. For a filename with a predictable extension, splitting at a period can expose the pieces, but it is not always a safe way to find the extension: filenames can contain multiple periods, and a period can occur in a folder name. If you need the final extension, locate the last period in the filename portion rather than assuming there is only one.

For line-oriented text, split on the appropriate line break. Windows text commonly uses vbCrLf; some sources use vbLf alone. If the input may come from different systems, inspect or normalize its line endings first. Splitting on vbCrLf will not match a lone line-feed character.

A practical process for using the function is:

  1. Identify the exact separator in the source text, including whether it has multiple characters.
  2. Call Split and store its result in a Variant.
  3. Check for empty input if the code will use LBound or UBound.
  4. Loop through the returned indexes or read the fields you need, accounting for missing or empty fields.
  5. Validate the parsed values before using them to update a workbook or perform another action.

Common problems and misconceptions

Split does not trim whitespace from the pieces. Splitting "red, green" on a comma produces "red" and " green", with a leading space in the second item. If that space is unwanted, apply Trim to each element as you process it. Trim removes leading and trailing spaces; it does not collapse multiple spaces inside a value.

Another misconception is that Split fully parses CSV files. It does not understand CSV quoting rules. A field such as "Smith, Jane" contains a comma that should remain inside one field in valid CSV, but a basic split on commas will break it into two pieces. For actual CSV data, use a CSV-aware import or parsing method rather than relying on Split alone.

Likewise, Split does not automatically write its results into worksheet cells. VBA code must transfer the values to a range if that is the goal. A delimiter mismatch can also produce an unexpected result: if the input uses semicolons but the code searches for commas, the whole input may remain one element. Inspect a representative input string and test edge cases before relying on the output.

Split compared with other ways to separate text

Choose the tool that matches where the work belongs. Use VBA Split when a macro needs an array of pieces for further logic. In Microsoft 365 Excel, the worksheet function TEXTSPLIT can split text directly into cells; it is available in Microsoft 365 and Excel 2024, not every older Excel release. Excel’s Text to Columns feature is another option for a one-time or user-driven separation of a worksheet column. These tools have different outputs and are not interchangeable: Split returns an in-memory VBA array, while the worksheet function returns a spill range and Text to Columns changes worksheet data.

For simple separators, Split is concise. For complex formats with quoting, escaped separators, or nested structure, a purpose-built parser is more appropriate. The more irregular the source, the more important it is to validate the result rather than assuming every record has the same number of fields.

Frequently Asked Questions

Does VBA Split start its array at 0 or 1?
It starts at 0. A result with three items has indexes 0 through 2, even if the module uses Option Base 1.

What happens if I leave out the delimiter?
The default delimiter is a space, so the function splits at spaces. If the source uses a comma, tab, pipe, or another separator, pass it explicitly.

Can Split use a delimiter with multiple characters?
Yes. Pass the complete delimiter string, such as "::", and VBA looks for that sequence as the separator.

How do I split without regard to letter case?
Pass vbTextCompare as the fourth argument to request text comparison. For example, include a limit argument before it, such as Split(source, delimiter, -1, vbTextCompare).

Why does UBound fail after Split?
If the expression was a zero-length string, Split returns an empty array, so there is no upper bound to retrieve. Check that the input is nonempty before calling UBound, and handle that case separately.

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.