VBA For Each Split: Loop Through Text Parts

VBA & macros · Updated November 22, 2026

To loop through text separated by a delimiter in VBA, use For Each with the array returned by Split, and declare the loop variable as a Variant. For example: For Each part In Split(text, ","). This works in desktop Excel VBA on Windows and Mac; the loop visits each piece of the text in order.

A basic For Each Split example

This small macro splits a comma-separated string and prints each part in the Visual Basic Editor’s Immediate Window:

Option Explicit

Sub ShowSplitParts()
    Dim sourceText As String
    Dim part As Variant

    ' Split the text at each comma.
    sourceText = "red,green,blue"

    For Each part In Split(sourceText, ",")
        Debug.Print CStr(part)
    Next part
End Sub

The output is red, green, and blue, each on its own line. In the VBA editor, open the Immediate Window with View > Immediate Window. On Windows, Alt+F11 opens or returns to the VBA editor from Excel; on Mac, use Developer > Visual Basic, or the Visual Basic Editor command available in Excel’s menus. Before running any macro on important data, save a copy of the workbook: macro actions cannot be reversed with Ctrl+Z.

The essential structure is For Each part In Split(sourceText, ","), followed by the statements to run for each part and Next part. You can store the result of Split in a variable first, but for simple loops it is valid to use the function call directly in the For Each statement.

What Split returns and how it divides text

Split is a VBA function that divides a string wherever it finds a specified delimiter. It returns a one-dimensional array of text values inside a Variant. With Split("red,green,blue", ","), the array contains three elements: “red”, “green”, and “blue”. Its indexes start at zero, so those elements are at indexes 0, 1, and 2.

The function’s general form is Split(expression, [delimiter], [limit], [compare]). The expression is the text to divide. The delimiter is the separator to look for; when omitted, VBA uses a space. The optional limit sets the maximum number of pieces to return, while compare controls how the delimiter is matched. If you do not need those options, the two-argument form is usually clearest.

For instance, splitting "North|South|East" with a pipe delimiter produces three items. Splitting "one two three" without specifying a delimiter uses spaces. The delimiter is a literal string, not a pattern: a delimiter of "||" means VBA looks for two adjacent pipe characters, not either pipe character separately.

Why the For Each variable should be Variant

A frequent source of errors is declaring the loop variable as String. In VBA, the control variable in a For Each loop over an array must be a Variant (or an Object when looping through objects). Since Split returns an array held in a Variant, declare the item variable as Variant, even though each item represents text.

Convert the item to a string when passing it to code that expects a String or when you want string-specific handling. CStr(part) makes that conversion explicit. For ordinary Split results it may seem unnecessary, but it documents the intended type and can make code easier to follow. Use Option Explicit at the top of a module so VBA requires variables to be declared; this catches many spelling mistakes before they cause confusing behavior.

Do not confuse the array with one of its elements. A variable declared as Variant can hold the array returned by Split, and another Variant can hold each element while the loop runs. A variable declared as String is appropriate for the original text, but not as the For Each control variable.

Writing each part into worksheet cells

A common Excel task is to read a delimited value from one cell and place each piece in a column. The following macro takes the text in A2, splits it at commas, trims spaces at the edges of each piece, and writes the results down column B beginning at B2:

Option Explicit

Sub SplitCellToColumn()
    Dim ws As Worksheet
    Dim sourceText As String
    Dim part As Variant
    Dim outputRow As Long

    Set ws = ActiveSheet
    sourceText = CStr(ws.Range("A2").Value)
    outputRow = 2

    ' Write one trimmed piece to each successive row.
    For Each part In Split(sourceText, ",")
        ws.Cells(outputRow, 2).Value = Trim$(CStr(part))
        outputRow = outputRow + 1
    Next part
End Sub

For input apple, pear, orange in A2, the macro writes apple to B2, pear to B3, and orange to B4. It does not clear cells below the output, so old results may remain if a later input has fewer pieces. Choose an empty destination area or add deliberate cleanup suited to your workbook before using it. The macro also writes to the active sheet, so check that the correct sheet is selected first.

In this example, Trim$ removes leading and trailing ordinary spaces from each piece. It does not remove every possible whitespace character, such as a tab or a nonbreaking space. If data comes from web pages or other systems, inspect the actual characters when trimming appears not to work. Also, converting an Excel error value with CStr can raise an error; check a cell with IsError first if the source may contain worksheet errors.

Empty pieces, extra spaces, and repeated delimiters

Split preserves empty pieces created by adjacent delimiters or delimiters at the beginning or end of the text. For example, splitting "A,,C" at commas produces three elements: “A”, an empty string, and “C”. Splitting ",A," produces an empty first element, “A”, and an empty final element. This behavior matters when each position represents a field: removing empty entries would change the positions and could misalign the data.

If empty fields are not meaningful in your task, test each part before processing it. For example, an If Len(Trim$(CStr(part))) > 0 Then condition can skip pieces that contain only ordinary spaces. If empty fields are meaningful, do not skip them; handle them as blank values so later fields stay in their intended positions.

An empty expression is a special case: Split("") returns an empty array, so a For Each loop has no iterations. A nonempty string containing delimiters can still produce empty elements. Those are different situations. If a macro must display a message or clear an output when the source is blank, check the source text before entering the loop rather than expecting the loop to run once with a blank value.

Using Split options and choosing a delimiter

The optional limit argument is useful when you want to divide only the first few sections and leave the remainder together. For example, a limit of 2 applied to "first,second,third" returns two pieces: “first” and “second,third”. The default limit, -1, returns all available pieces. This can help when the final section may itself contain the delimiter, although it does not provide a general quoting or escape system.

The optional compare argument determines the comparison mode used to find the delimiter. In typical Excel code, a comma or another punctuation delimiter does not have a case distinction, so the default is usually sufficient. For a letter delimiter, comparison settings can matter. VBA’s vbBinaryCompare compares character values, while vbTextCompare uses text-style case-insensitive comparison. If the delimiter is a letter and case handling matters, specify the desired comparison explicitly and check that it matches the workbook’s needs.

Pick the delimiter that actually separates fields in the source. A comma is not interchangeable with a semicolon, tab, line break, or multiple spaces. A line break in VBA can be represented by vbLf or vbCrLf, depending on the source. When text has inconsistent separators, first determine which characters are present; replacing or normalizing them can be more reliable than assuming one delimiter will match all records.

For Each versus an indexed For loop

Use For Each when you simply need to process every part and do not need its numeric position. It keeps the code compact and avoids manually managing array bounds. If your code needs to refer to an element’s index, use a numeric loop with LBound and UBound instead. For a normal nonempty Split result, the lower bound is zero, but using the bound functions makes the indexing intent explicit.

Be careful with an empty source when using an indexed loop. Because Split("") returns an array with no elements, trying to use LBound or UBound on that result can cause an error. Check for blank input before asking for its bounds. A For Each loop naturally performs zero iterations for an empty array, which is one reason it is convenient for simple processing.

Neither loop type changes the data or makes the split more accurate. The choice is about how your code needs to access the results. Choose For Each for “do this to every piece”; choose an indexed loop when you need to compare positions, write to a matching array index, or use the current position in other calculations.

Common mistakes and CSV limitations

  • Declaring the loop variable as String: use Variant for the For Each control variable over the Split array.
  • Using the wrong delimiter: a comma does not match a semicolon or a tab, and a multi-character delimiter is matched as a whole.
  • Expecting empty fields to disappear: adjacent separators create empty elements; decide whether to preserve or skip them.
  • Assuming Split understands quoted text: it does not parse CSV rules or protect commas inside quotation marks.
  • Expecting Ctrl+Z to reverse a macro: save a backup before running code that writes or changes worksheet data.

The CSV limitation is especially important. If a field contains a quoted comma, such as "Smith, Jane",42, a plain Split at each comma will divide the name into two pieces even though a CSV reader treats it as one field. Similarly, quotation marks, escaped quotes, and line breaks inside quoted fields require CSV-aware parsing. For actual CSV files, consider Excel’s built-in import tools or Power Query rather than splitting each line with a basic VBA loop. Split is suitable for simple, consistently delimited text, not a complete CSV parser.

When to use another Excel method

VBA is useful when splitting is part of a repeatable macro, needs to run alongside other automation, or depends on custom rules. For a one-time worksheet transformation, Excel’s Text to Columns command may be quicker: select the data, then use Data > Text to Columns and follow the delimiter options. In Microsoft 365, the TEXTSPLIT worksheet function can also divide text into cells without a macro. It is available in Microsoft 365 and Excel 2024, but not in every older Excel release.

Power Query is a stronger alternative for repeated imports and cleanup of structured text files. It provides a guided way to split columns by a delimiter and refresh the transformation when the source changes. Choose based on the job: use a worksheet function for formula-driven results, Text to Columns for a quick manual conversion, Power Query for repeatable data preparation, and VBA when code-based automation or custom processing is needed.

Frequently Asked Questions

Can I write For Each item In Split directly?
Yes. Declare item as a Variant, then use For Each item In Split(text, delimiter). Store the result in a Variant array first only when you need to reuse it or inspect it separately.

Why does my For Each Split loop not run?
If the expression is an empty string, Split returns an empty array and there are no iterations. Confirm that the source cell contains text and that your code is reading the expected cell and worksheet.

How do I split on a space?
Use Split(text, " ") for a literal ordinary space. If you omit the delimiter, VBA also uses a space. Multiple adjacent spaces create empty elements; normalize the text first if you want them treated as one separator.

Does Split handle commas inside quotation marks?
No. Split looks for the delimiter wherever it occurs and does not interpret quotation marks as CSV syntax. Use a CSV-capable import or parsing method when quoted fields can contain commas.

Can I loop through the parts in reverse?
For Each processes the array in its normal element order; it does not provide a reverse-loop option. If reverse order matters, store the Split result in a Variant array and use a numeric loop from UBound down to LBound, after handling the possibility of blank input.

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.