VBA Split in Excel — Turn One String Into an Array (the Right Way)

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-09

TL;DR — Split takes one string, cuts it at every occurrence of a delimiter, and hands you a zero-based array of the pieces. It is the exact inverse of joining strings together:

Sub SplitDemo()
    Dim parts() As String
    parts = Split("Jones,Sarah,Finance", ",")   ' cut at every comma

    Debug.Print parts(0)            ' Jones   <- first piece is index 0, not 1
    Debug.Print parts(2)            ' Finance
    Debug.Print UBound(parts)       ' 2       <- last index, so 3 items = 0,1,2
End Sub

The full signature, with the two arguments almost nobody uses:

Split(expression, [delimiter], [limit], [compare])
'      the string   default " "  max parts  vbTextCompare = case-insensitive

The mental model: Split is the un-concatenate

If "a" & "," & "b" glues pieces into one string, Split is the tool that pulls them back apart. You hand it a string that already has structure baked in — a CSV line, a file path, a Last,First name, a pipe-delimited log row — and it returns the fields as an array you can loop over.

That reframing matters because it tells you when to reach for Split versus its neighbours: use Split when a single delimiter character marks every boundary. If you instead need to grab a piece by its position (characters 3–5), that is a job for Mid/Left/Right. If you need to find where a marker is before cutting, that is InStr. Split is for "this string is a list, give me the list."

The one rule: the array is always zero-based — even with Option Base 1

This is the rule that separates working code from a silent bug:

No matter what Option Base you set at the top of the module, the array that Split returns starts at index 0. The first field is parts(0). The last is parts(UBound(parts)).

Here is the bug that costs people an afternoon. They know arrays and write a loop starting at 1:

' ⚠ WRONG — silently drops the first field
For i = 1 To UBound(parts)
    Debug.Print parts(i)
Next i

That loop runs without error. It just never touches parts(0) — so "Jones" vanishes and nobody notices until a report is missing every first name. The fix is to always iterate from 0:

For i = 0 To UBound(parts)         ' 0 To UBound — covers every piece
    Debug.Print parts(i)
Next i

UBound returns the last index, not the count. Three items means UBound = 2. If you need the count, it is UBound(parts) + 1. Burn that in: Split is 0-based, UBound is the top index, loops run 0 To UBound.

The second trap: Split is literal, not smart

Split matches the delimiter exactly as written — it does not trim, it does not understand quotes, it is not a regex. Two consequences bite people constantly.

It keeps the spaces. Splitting "a, b, c" on a comma gives you "a", " b", " c" — the pieces after the first carry a leading space. If you compare them to "b" they will not match. Trim each piece, or split on ", " and accept that one stray format breaks it:

parts = Split("a, b, c", ",")
For i = 0 To UBound(parts)
    parts(i) = Trim(parts(i))      ' clean each field — see vba-mid / Trim
Next i

No-match is not an error. Splitting a string that does not contain the delimiter returns a one-element array — index 0 holds the whole original string. So always guard before assuming you got multiple fields:

parts = Split(userInput, ",")
If UBound(parts) < 1 Then
    MsgBox "Expected a comma-separated value."
    Exit Sub
End If

And the sharp edge: Split("") on an empty string returns an array whose UBound is −1. A 0 To UBound loop safely does nothing, but reaching for parts(0) blows up with subscript out of range. Check the input, or check UBound(parts) >= 0, before indexing.

Two arguments worth knowing: limit and compare

limit caps how many pieces you get — the last element keeps everything that is left, undivided. Perfect for "split off the key, leave the value alone":

parts = Split("error: file not found: budget.xlsx", ":", 2)
' parts(0) = "error"
' parts(1) = " file not found: budget.xlsx"   <- rest is intact, colons and all

compare:=vbTextCompare makes an alphabetic delimiter case-insensitive — splitting "1xRED2Xblue" on "x" only finds both x's if you ask for a text compare. Niche, but it is the answer when a letter-delimiter split misses half its boundaries.

The opinion: Split is not a CSV parser — stop bolting regex onto it

The moment someone has a CSV file, the instinct is Split(line, ","). It works until the day a field contains a comma inside quotes — "Smith, Jr.",Sales — and Split happily cuts "Smith" and " Jr." into two fields, shifting every column after it. The data is now silently wrong.

My rule: Split is for delimited data you control, not for parsing the CSV format. Internal pipe-delimited logs, a Last,First cell you built yourself, a path you split on \ — perfect. But the instant the source is a real CSV export from someone else's system, Split is the wrong tool, and the answer is not a 40-line regex patch on top of Split. It is a proper parser (Power Query, a real CSV routine, or a library) that understands quoting. Knowing where Split stops is the difference between a robust import and a column-shift bug you find in production.

When to use which

Common Split mistakes (and the fix)

When the string wrangling outweighs the point — describe the job instead

Splitting is rarely the goal. The goal is "import this messy export, clean the names, match them to my master list." By the time you have armed the split, trimmed each field, guarded the empty case, and handled the row that broke the format, the parsing plumbing dwarfs the one thing you actually wanted. ExcelMaster Agent lets you state that goal in plain English — "split the Name column into First and Last, trim them, flag rows that don't fit" — and it generates Python that handles the edge cases and backs up your file first. Try it free →

Related guides

• VBA Mid, Left & Right — Extract a Substring by Position
• VBA Replace — Swap Text & the Traps in Its Arguments
• VBA InStr — Find Text Inside a String
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

What does Split do in VBA? Split divides one string into an array of substrings, cutting at every occurrence of a delimiter (a space by default). Split("a,b,c", ",") returns a three-element array: "a", "b", "c". The array is zero-based, so the first element is index 0.

Why is my VBA Split array missing the first item? Almost always because your loop starts at 1. Split returns a zero-based array regardless of Option Base 1, so the first field is index 0. Loop For i = 0 To UBound(arr) to include it.

How do I get the number of elements from Split? Use UBound(arr) + 1. UBound returns the highest index, not the count — three items have UBound = 2. For an empty input string, UBound is −1, so guard before indexing element 0.

Can VBA Split use multiple delimiters? Not directly — Split takes one delimiter. The common workaround is to Replace the other delimiters with your main one first, then Split once. For anything more complex, use a regex or a parser rather than chaining replacements.

Is VBA Split case-sensitive? Only matters when your delimiter is a letter. By default Split matches the delimiter exactly (binary). Pass compare:=vbTextCompare as the fourth argument to match an alphabetic delimiter case-insensitively.