In VBA, use the Split function to divide a string wherever a specified delimiter appears; it returns the pieces in a zero-based array. For example, splitting "red,green,blue" on a comma gives three elements, which you can read as parts(0), parts(1), and parts(2).
How VBA Split works
Split searches a string for the delimiter you provide and separates the text at each match. The delimiter is not included in the returned pieces. The function is useful when a value contains a consistent separator, such as commas in a list, tabs between fields, or a pipe character between imported records.
The function returns a one-dimensional array of strings inside a Variant. It does not automatically place those strings into worksheet cells: your code must loop through the array or otherwise transfer its values. Nor does Split interpret commas inside quoted CSV fields. It performs a straightforward delimiter split, not full CSV parsing.
Basic syntax and a working example
The syntax is Split(expression, [delimiter], [limit], [compare]). The expression is the text to divide. The optional delimiter identifies the separator; if omitted, VBA uses a space. The limit and compare arguments let you control how many pieces are returned and how the delimiter is matched.
This Excel VBA example reads a comma-separated string from cell A1 and writes the pieces across row 1, starting in B1:
Option Explicit
Sub SplitCellText()
Dim sourceText As String
Dim pieces As Variant
Dim target As Worksheet
Dim i As Long
' Read the source text from the active sheet.
Set target = ActiveSheet
sourceText = CStr(target.Range("A1").Value)
' Split returns an empty array for a zero-length expression.
If Len(sourceText) = 0 Then
MsgBox "Cell A1 is empty."
Exit Sub
End If
' Separate on commas, then write each piece from column B onward.
pieces = Split(sourceText, ",")
For i = LBound(pieces) To UBound(pieces)
target.Cells(1, i + 2).Value = pieces(i)
Next i
End Sub
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, and this example writes over cells in row 1 from column B onward. It does not clear old values farther to the right, so check the destination cells first.
Delimiter, limit, and comparison options
A delimiter can be more than one character. For example, if the text is separated by ::, pass that entire string as the delimiter; VBA looks for the exact sequence, not for any one character from a group. A tab delimiter can be supplied with vbTab, while a line break in Windows text is commonly represented by vbCrLf.
The optional limit sets the maximum number of pieces. A value of 1 returns the whole expression as one element. A value of 2 splits at the first matching delimiter and leaves the rest of the text in the second element. The default limit, -1, returns all pieces. This is handy when the first separator divides a record into a key and a remainder that may itself contain separators.
The compare argument controls how VBA matches the delimiter. Use vbBinaryCompare for a binary, case-sensitive match or vbTextCompare for a text comparison that ignores letter case. If you omit this argument, the module’s comparison setting applies; in a module without an Option Compare Text statement, the usual default is binary comparison. Supplying the comparison explicitly makes the intended behavior easier to see.
Reading and looping through the returned array
Because the result is an array, use LBound and UBound to find its valid index range instead of assuming that it has a particular number of elements. For a normal nonempty result from Split, the lower bound is zero. A loop from the lower bound to the upper bound visits each piece once, including any empty pieces created by adjacent delimiters.
A Variant variable is a straightforward way to hold the result, as in the example. It lets the code receive the Variant array returned by Split without needing to declare a fixed-size array in advance. You can inspect a particular item by index, pass the array to another procedure, or use a For Each loop with a Variant loop variable when you do not need the index.
Remember that array indexes and worksheet column numbers are different things. In the example, array index 0 is written to column 2, which is B; the expression i + 2 makes that conversion. If you write values vertically instead, use the row number as the changing coordinate and keep the column fixed.
Delimiters, empty fields, and real-world text
Consecutive delimiters produce empty fields between them. For instance, splitting "a,,c" on a comma returns "a", an empty string, and "c". A trailing delimiter also represents an empty final field. This matters for structured data: removing empty results without thinking about the format could shift later values into the wrong columns.
When the delimiter does not occur in the expression, the result contains the original expression as a single element. When the expression is a zero-length string, Split returns an empty array with no elements. That is an important distinction: an empty input does not behave like a one-item array containing an empty string. Code that calls UBound should account for empty input first, as the example does.
If the source value may contain a worksheet error, converting it with CStr can fail; handle or validate error values before conversion when that is possible in your workbook. Likewise, trim whitespace only if the data rules call for it. Applying Trim to every piece may be helpful for names separated by comma and space, but it can also remove meaningful leading or trailing spaces in data where they are significant.
Using Split in Excel and opening the VBA editor
Split is a VBA function, not a worksheet formula. To use the example in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and put the procedure there. Run it from the editor or use Alt+F8 to open the Macro dialog. On Mac, Option+F11 commonly opens the editor and Option+F8 opens the Macro dialog; depending on the keyboard, you may also need to hold Fn. You can also use the Developer tab’s Visual Basic and Macros commands.
Macros require a macro-enabled workbook format such as .xlsm if you want to retain the VBA code. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros. Open the file in the desktop application to run this code. The editor and macro shortcuts differ by keyboard and settings, so the Developer tab is a useful alternative if a shortcut does not work.
Split compared with other options
For a quick worksheet-only split in Microsoft 365 or Excel 2024, the worksheet function TEXTSPLIT can return separated values directly into cells. That may be simpler when the task is part of a formula-driven sheet. Split is more appropriate when the logic belongs in a macro, when code needs to inspect or transform each part, or when you are automating a larger workflow. These are separate features: using one does not require the other.
Excel’s Text to Columns command is another option for a one-time worksheet operation. Select the source cells, then choose Data > Text to Columns and follow the delimiter steps. It changes worksheet data rather than returning an array for VBA code to process, so it is not a direct substitute inside a procedure. For a format with quoted fields, escaped separators, or other CSV rules, use a CSV-aware import or parser instead of assuming that a simple Split call will interpret those rules.
The related VBA function Join goes in the opposite direction: it combines array elements into one string, placing a chosen delimiter between them. A common pattern is to split text, adjust selected elements, and then join the array again. Keep in mind that joining may not recreate the original text exactly if the code changes empty values, spacing, or delimiters.
Common problems and how to avoid them
- Wrong separator: A space is the default delimiter, not a comma. Pass the delimiter explicitly when the data uses commas, tabs, or another character.
- Unexpected empty elements: Repeated separators create empty strings. Check whether those fields are meaningful before removing or skipping them.
- Subscript out of range: The requested index may not exist. Use
LBoundandUBound, and handle zero-length input before callingUBound. - Text remains attached: If the delimiter is absent or spelled differently, the result is one unchanged element. Check for extra spaces, line endings, or a delimiter that contains multiple characters.
- CSV fields split incorrectly: A comma inside quoted text is still a comma to
Split. Use a parser designed for CSV rather than a basic string split.
A useful debugging step is to examine the original string and the delimiter separately, including invisible characters. A value that looks like a space may be a tab, and Windows line breaks may contain both carriage return and line feed. When output seems shifted, inspect empty array elements as well as the visible text.
Frequently Asked Questions
Does VBA Split start at zero or one?
It returns a zero-based array, so the first piece is normally at index 0. Use LBound rather than hard-coding the lower bound in reusable code.
What happens if I leave out the delimiter?
VBA uses a space as the delimiter. If your source text uses a comma, tab, or another separator, specify it explicitly.
Can Split separate text on more than one character?
Yes. Pass a multi-character delimiter such as ::; VBA matches that exact sequence. It does not treat the delimiter as a set of alternative characters.
Can Split handle quoted CSV values?
Not by itself. It splits at every matching delimiter, including a comma inside quotes. Use a CSV-aware import or parser when quoted fields or escaped characters must be interpreted correctly.
Is Split an Excel formula?
No. It is a VBA function used in code. For worksheet formulas in Microsoft 365 or Excel 2024, TEXTSPLIT may suit the task; in Excel for the web, VBA macros cannot be run.
Related
- Conditional Formatting for Empty Cells in Excel Explained
- Databricks Notebook: How to Split a Cell
- 2 Criteria Conditional Formatting in Excel: Two Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Call Another Macro: How VBA Calls WorkYes. In Excel VBA, one macro can run another by calling its Sub procedure by name, such as…
- VBA InputBox: How It Works, Limits, and When to UseYes—VBA’s InputBox is fine for a quick, simple prompt, such as asking for a name or a short…
- Excel VBA Find a Substring in a StringIn Excel VBA, use InStr to find a substring inside a string: it returns the position of the…
- COUNTIF in Excel VBA: Syntax, Examples, and TipsIn Excel VBA, count cells that meet one condition with Application.WorksheetFunction.CountIf(range, criteria). For example, if A2:A100 contains status…
- VBA User Input Box: InputBox, Validation, and ExamplesA VBA user input box is a small dialog that asks someone to enter text, a number, or…
- Access VBA Match Function: What It Does and How to Use ItMicrosoft Access does not provide Excel’s worksheet MATCH function as a native VBA function. To find a value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.