Excel VBA Split: Separate Text with the Split Function

Excel VBA’s Split function separates a string wherever it finds a specified delimiter and returns the resulting pieces as an array. For example, parts = Split("red,green,blue", ",") produces three values—red, green, and blue—that VBA code can read, edit, or place in worksheet cells.

What Excel VBA Split Does

Split is a built-in Visual Basic for Applications function, not an Excel worksheet function. It is useful when a cell or text file contains several values joined together by a repeated character or text marker, such as commas, pipes, semicolons, or line breaks. VBA breaks the original string at each match and returns the pieces in an array.

For example, the text North|Central|South can be split using the pipe character as the delimiter. The resulting array contains three strings. A macro can then put those strings into separate cells, check a particular piece, or pass the pieces to another procedure. The function does not itself write anything to the worksheet; your code decides what to do with its returned array.

This is especially handy when processing batches of rows or automating a repeatable cleanup task. For a one-time split of a small range, Excel’s built-in Text to Columns tool may be quicker. If the goal is to split text directly with a worksheet formula, use a supported text function instead.

How Split Works and What It Returns

The general VBA syntax is Split(expression, [delimiter], [limit], [compare]). The expression is the string to break apart. The delimiter is the character or sequence that marks a boundary. The optional limit controls how many pieces are returned, while compare controls whether delimiter matching is case-sensitive. The function returns an array of strings, even if there is only one piece.

VBA arrays returned by Split are zero-based: the first piece is at index 0, the second at index 1, and so on. That differs from the common expectation that the first item is numbered 1. Use LBound(parts) and UBound(parts) to find the first and last valid indexes rather than assuming how many pieces exist.

If the delimiter is not present, the result is a one-element array containing the original expression.
If the delimiter appears more than once in a row, the pieces between those delimiters are empty strings. For example, splitting a,,c on a comma returns a, an empty value, and c.
If the delimiter is omitted, VBA uses a space as the delimiter. If the delimiter is an empty string, the result is the original expression as one piece.
An empty expression returns an empty array, so code that processes its bounds should account for blank input.

A delimiter can be more than one character. For example, splitting one::two::three on :: treats that exact pair of colons as the separator. It does not treat either colon independently.

A Basic VBA Example

This small procedure shows how to split a string and display each piece in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows, run the procedure, and look for the three output lines.

Option Explicit

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

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

    ' Split arrays start at index zero.
    For i = LBound(parts) To UBound(parts)
        Debug.Print parts(i)
    Next i
End Sub

The output is red, green, and blue, each on its own line. The variable is declared as a Variant because the function returns an array. The loop uses the array’s actual bounds, which is more reliable than writing a fixed range such as 0 To 2 when input lengths can vary.

Split a Worksheet Column into Separate Cells

For a practical worksheet example, suppose column A contains comma-separated text, with a header in A1 and records beginning in A2. The following macro places each comma-separated piece in columns B, C, and onward on the same row. It trims spaces at the outside of each piece, so red, green becomes red and green.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and this procedure writes values into the worksheet. The example assumes the source cells contain text or are blank, and that writing from column B onward will not destroy data you need.

Option Explicit

Sub SplitColumnAIntoCells()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim c As Long
    Dim sourceText As String
    Dim parts As Variant

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        If Not IsError(ws.Cells(r, "A").Value) Then
            sourceText = CStr(ws.Cells(r, "A").Value2)

            If Len(sourceText) > 0 Then
                parts = Split(sourceText, ",")

                For c = LBound(parts) To UBound(parts)
                    ws.Cells(r, c + 2).Value = Trim$(CStr(parts(c)))
                Next c
            End If
        End If
    Next r
End Sub

To add it, open the Visual Basic Editor, choose Insert > Module, paste the code, and run SplitColumnAIntoCells. On Windows, Alt+F11 opens the editor; on a Mac, the shortcut depends on the keyboard and may require Option+F11 or Fn+Option+F11. You can also use the Developer tab’s Visual Basic command. If the Developer tab is hidden, enable it in Excel’s ribbon settings.

The macro does not clear old output before writing. If an earlier run split a row into four pieces and the revised input produces only two, the previous values in the extra output cells can remain. Check and clear the intended output area before rerunning, or adapt the procedure to clear a carefully chosen range first. Also verify that column A is the correct source column and that the active sheet is the one you intend to change.

Delimiters, Limits, and Text Matching

The delimiter should match the actual structure of the text. A comma is appropriate for a simple comma-separated string; a pipe can be a useful separator when commas occur inside the values. VBA does not guess which character is intended, so choosing a delimiter that does not appear within the data is important.

The optional limit argument can restrict how many substrings are returned. A positive limit returns no more than that number of pieces; if the original text contains additional delimiters, the remaining text stays together in the last piece. This can help when only the first few fields matter. The default limit, -1, returns all pieces.

The optional compare argument affects text comparison for the delimiter. vbBinaryCompare performs a binary, case-sensitive comparison, while vbTextCompare uses a text comparison that is not case-sensitive. This distinction matters most when the delimiter contains letters, rather than punctuation. If you omit the argument, the comparison follows the module’s Option Compare setting, or the default binary comparison when no such setting is specified.

Common Problems and Important Limitations

A frequent surprise is that Split is not a full CSV parser. If a value contains a comma inside quotation marks, a basic comma split still treats that comma as a separator. For example, text such as "Smith, Jordan",42 needs quote-aware parsing if the comma inside the name should remain part of one field. Do not use a simple Split call to parse arbitrary CSV files with quoted fields, escaped quotes, or other CSV rules.

For structured CSV files, use Excel’s import options or a suitable data-processing approach that understands CSV quoting. In Microsoft 365 for Windows, Power Query is available through the Data tab for importing and transforming supported sources; availability and some capabilities vary by Excel platform. For a straightforward range with a consistent delimiter, Data > Text to Columns can be faster than writing a macro.

Another common issue is an unexpected blank piece. Two consecutive delimiters create an empty string, and a delimiter at the beginning or end can create an empty first or last piece. Decide whether those empty values are meaningful before removing them. Trimming spaces with Trim$ removes leading and trailing spaces from each piece, but it does not remove empty array elements or change spaces inside a value.

Finally, the function splits text; it does not automatically convert pieces into numbers, dates, or other data types. When writing results to cells, Excel may interpret values according to cell formatting and its normal data-entry behavior. If exact text preservation matters—for example, for identifiers with leading zeros—consider formatting destination cells as Text before writing, and verify the result.

Choosing Between VBA, Text to Columns, and a Formula

Choose VBA when the split is part of a repeatable workflow, needs custom logic, or must be applied automatically to many rows. A macro can split each row, trim values, skip error cells, and continue with other processing. It requires a macro-enabled workflow and a user who can run macros; Excel for the web cannot create, run, or edit VBA macros.

Choose Text to Columns for a one-off split of a selected column. Select the source cells, then choose Data > Text to Columns and follow the wizard to select a delimiter or fixed-width layout. Review the destination setting before finishing, because the output can overwrite neighboring cells. The feature is available in desktop Excel, including Windows and Mac, though the interface can differ slightly.

For a formula-based split, Microsoft 365 and Excel 2024 include TEXTSPLIT. With the text red,green,blue in A2, enter =TEXTSPLIT(A2,",") in an empty cell; it returns the three values across adjacent cells. This is useful when results should recalculate as the source changes. Unlike VBA, it does not require a macro, but it is not available in every older Excel release.

In short, use Split for programmable string handling, Text to Columns for a quick manual operation, and TEXTSPLIT when a dynamic worksheet formula is the best fit. Whichever method you choose, check for embedded delimiters, blank fields, and enough empty space for the results.

Frequently Asked Questions

Does VBA Split start counting at 0 or 1?
It returns a zero-based array, so the first piece is at index 0. Use LBound and UBound to loop safely through the returned array.

How do I split text using a pipe or semicolon?
Pass that character as the delimiter, such as Split(sourceText, "|") for a pipe or Split(sourceText, ";") for a semicolon. The delimiter must match the text exactly.

Can Split put the results into worksheet cells by itself?
No. It returns an array. VBA code must assign the array elements to cells or use another method to display or process them.

Why does Split return blank values?
Adjacent delimiters, or a delimiter at the beginning or end of the string, create empty pieces. Check the source data and decide whether those empty fields should be kept or handled separately.

Can I use VBA Split in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for a VBA solution, or consider Text to Columns or TEXTSPLIT if available in your version.

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.