VBA Split Function: Syntax, Examples, and Common Fixes

VBA & macros · Updated November 22, 2026

In VBA, Split divides a string wherever it finds a specified delimiter and returns the resulting pieces in a zero-based array. For example, Split("red,green,blue", ",") returns three items: red, green, and blue.

What the VBA Split function does

Split is a Visual Basic for Applications function for breaking text into smaller strings. It is useful when a macro needs to process values separated by commas, spaces, tabs, pipes, or another known character or string. The result is an array, so each piece can be read by its position, checked, or passed to another procedure.

For instance, a macro might split a list of names separated by semicolons, separate a date from a time when a source uses a consistent separator, or parse a simple line of delimited text. Split works on the text supplied to it; it does not automatically read a worksheet range or write the results into cells.

One important distinction: VBA’s Split is not an Excel worksheet function. Typing =SPLIT(...) into a cell does not call this VBA function. In Microsoft 365 and Excel 2024, the worksheet function TEXTSPLIT can split cell text in a formula. Use VBA Split inside a macro or other VBA code.

Syntax and arguments

The function accepts a required string expression and up to three optional arguments, in this order: delimiter, limit, and comparison mode. If you omit the delimiter, VBA uses a space. If you omit the limit, it returns all the pieces. If you omit the comparison argument, the delimiter comparison is binary, meaning it distinguishes uppercase and lowercase characters.

  • Expression: The string to divide.
  • Delimiter: The character or string marking each boundary. It can be more than one character.
  • Limit: The maximum number of pieces to return. The default value, -1, means return all pieces.
  • Compare: A constant controlling how VBA matches the delimiter, such as vbBinaryCompare or vbTextCompare.

The returned value is a Variant containing a one-dimensional array of strings. Its indexes start at zero, not one. That matters when you retrieve an item or write a loop: the first piece is at index 0. Use LBound and UBound to get the array’s lower and upper indexes rather than assuming a particular number of pieces.

A basic example and its result

This macro splits a comma-separated string and prints each part in the Visual Basic Editor’s Immediate window. In the Windows desktop version of Excel, open the editor with Alt+F11, insert a standard module, and run the procedure. The output is three lines, with indexes 0, 1, and 2 alongside the words.

Sub ShowSplitParts()
    Dim sourceText As String
    Dim parts As Variant
    Dim i As Long

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

    ' Print each zero-based index and its value.
    For i = LBound(parts) To UBound(parts)
        Debug.Print i, parts(i)
    Next i
End Sub

The Immediate window output is 0 red, 1 green, and 2 blue. In Excel for Mac, VBA is available in the desktop app as well; the keyboard shortcut for the editor can depend on the keyboard’s function-key settings, so use the Visual Basic command on the Developer tab if the shortcut does not open it. Excel for the web does not run VBA macros.

Delimiters, spaces, and empty pieces

Split looks for the delimiter exactly as specified, subject to the selected comparison mode. If you use a comma as the delimiter, a semicolon will not divide the string. If a source uses a two-character separator such as ::, pass that complete string as the delimiter. When no delimiter is supplied, the default is a single space; it is not a general instruction to split on every kind of whitespace.

Repeated delimiters create empty pieces rather than being silently ignored. For example, splitting red,,blue, at a comma yields four items: red, an empty string, blue, and another empty string. This behavior can be useful because it preserves the positions of blank fields, but it can also surprise code that expects every item to contain text. A tab is different from a space, so pass the appropriate delimiter when handling tab-separated data.

An empty expression is a special case: Split returns an empty array, not an array containing one empty string. Code that immediately calls LBound or UBound on that result can fail. Check the input before splitting, or handle the empty-input case separately. Conversely, an empty delimiter returns the original expression as a single array item; it does not split the string into individual characters.

Using the limit and comparison options

The limit argument is handy when only the first few separators matter. With the string name|department|region, a limit of 2 produces two pieces: name and department|region. In other words, the remaining text stays together in the final piece. This is useful when the first field has a known separator but the rest may contain additional separators.

The comparison argument controls whether letter case matters when matching the delimiter. The default binary comparison treats X and x as different. To match a delimiter without regard to case, supply vbTextCompare. For example, this is relevant when a source may use either END or end as a marker. Text comparison follows VBA’s text-comparison rules, so use it deliberately rather than assuming it changes how the returned text is capitalized.

VBA also provides vbUseCompareOption, which uses the module’s Option Compare setting. That makes behavior depend on the setting at the top of the module, so an explicit comparison constant can make code easier to understand. In Excel VBA, avoid using vbDatabaseCompare; that option is intended for Microsoft Access database comparisons.

Working with the returned array

After splitting, retrieve a specific element with its index, or loop through the whole array. Because Split returns a Variant array, declaring the receiving variable as Variant is a straightforward choice. A frequent error is declaring a regular string variable for the result and then treating it as though it were an array. Another is starting a loop at 1, which skips the first item and can eventually request an index that does not exist.

If the number of pieces is predictable, you can check the upper bound before using a position. If it is not predictable, loop from the lower bound through the upper bound. Also consider trimming spaces after a split when the source uses spaces around delimiters: splitting red, green at a comma leaves a leading space in the second item. Split separates at the delimiter; it does not clean or normalize the resulting pieces for you.

For example, after splitting a line, a macro can inspect each item with Len, compare it with another string, or assign it to a cell. If writing values to a worksheet, ensure the destination cells are appropriate and not holding information that should be preserved. The function itself only creates the array; any worksheet changes come from the code that uses that array.

Common problems and practical limits

  • Using the wrong separator: A comma, semicolon, tab, and line break are different delimiters. Confirm the source text’s actual separator.
  • Unexpected empty items: Consecutive or trailing delimiters produce empty strings. Decide whether to keep, skip, or flag those fields.
  • Incorrect array bounds: The first index is 0. Use LBound and UBound, and handle an empty expression before asking for bounds.
  • Assuming CSV quoting is understood: Split is not a CSV parser. A comma inside a quoted field is still treated as a delimiter, and escaped quotes are not interpreted. Use a proper CSV-reading method when quoted fields or other CSV rules matter.
  • Expecting worksheet behavior: VBA Split runs in VBA code, not as a normal cell formula. For worksheet formulas, consider TEXTSPLIT where available.

Another useful distinction is that Split does not change the original string or remove spaces around the pieces. If the input is irregular, additional validation may be needed before using the fields. For example, a string that sometimes uses commas and sometimes semicolons needs preprocessing or a more specific parsing approach; selecting one delimiter will not automatically recognize both.

Split versus TEXTSPLIT and other approaches

Choose VBA Split when a macro needs to process a string in code, especially as part of a larger automation task. It is compact and returns an array that is easy to loop over. Choose the Excel worksheet function TEXTSPLIT when the goal is to display separated values in cells with a formula. TEXTSPLIT is available in Microsoft 365 and Excel 2024; it is not the same function as VBA Split, and formula availability depends on the Excel version.

For a simple, consistently delimited string, Split is usually enough. For true CSV files with quoted fields, embedded commas, or escaped quotation marks, use a CSV-aware import or parser instead of dividing at every comma. For text that needs to be divided using several different rules, consider whether a purpose-built parsing routine will be clearer and safer than chaining basic string operations.

To keep a VBA macro, save the workbook in a macro-enabled format such as .xlsm. Before running a macro that changes data, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z. On Mac, the same principle applies, though available file formats and macro behavior should be checked in the desktop Excel app being used. Never enable all macros globally just to run one file; follow your organization’s security guidance and only run code from a source you trust.

Frequently Asked Questions

Does VBA Split start at 0 or 1?
It returns a zero-based array, so the first item is at index 0. Use LBound and UBound to determine the actual bounds before looping.

What happens if the delimiter is missing?
If the delimiter does not occur in the expression, the result is a one-item array containing the original expression. If the delimiter is omitted entirely, VBA uses a space as the delimiter.

Can Split divide text at more than one delimiter?
Each call uses one delimiter string. For mixed separators, you can normalize the text first or use separate parsing logic. Replacing separators blindly may be unsafe if those characters can also appear within fields.

Can I use Split in an Excel cell formula?
No. VBA Split is available to VBA code, not as a built-in worksheet formula. In Microsoft 365 or Excel 2024, use the worksheet function TEXTSPLIT for formula-based splitting.

Why does Split return an empty array for blank text?
An empty expression is a special case and returns an array with no elements. Check whether the source string has content before using array-bound functions or accessing an item.

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.