Yes. In Excel VBA, use the Split function to divide a string at a delimiter and return the pieces as a zero-based array. For example, splitting red,green,blue at commas gives you three separate values you can inspect, loop through, or write into worksheet cells.
What VBA Split Does
Split is a Visual Basic for Applications function for breaking one string into smaller strings wherever a chosen delimiter occurs. A delimiter is the character or sequence that marks the boundary between parts: a comma in a list, a tab between fields, or a line break between lines. The delimiter is removed from the results; it is not included in any returned piece.
The result is an array held in a Variant variable. Its indexes start at zero, so a three-part result has indexes 0, 1, and 2—not 1, 2, and 3. That differs from worksheet row and column numbers, which start at 1, and is a frequent source of off-by-one errors when transferring pieces to cells.
In Excel, VBA is useful when the split is part of a larger macro or must be repeated across many records. It is not the same thing as entering a formula in a worksheet cell: the function runs in VBA code, usually from a macro, and returns values for that code to handle.
A Basic Comma-Separated Example
This macro splits a short comma-separated string, then prints each array index and value in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows. Before running a macro, save a copy of the workbook; macro changes cannot be undone with Ctrl+Z.
Option Explicit
Sub ShowSplitParts()
Dim sourceText As String
Dim parts As Variant
Dim i As Long
' Split the sample at each comma.
sourceText = "red,green,blue"
parts = Split(sourceText, ",")
' The array uses zero-based indexes.
For i = LBound(parts) To UBound(parts)
Debug.Print i, parts(i)
Next i
End Sub
The Immediate window shows three entries: index 0 with red, index 1 with green, and index 2 with blue. To use the values elsewhere, replace Debug.Print with the action your task requires, such as assigning a value to a variable or writing it to a worksheet cell. Be deliberate about the destination: a loop that writes to cells can overwrite existing data.
The variable is declared as a Variant because Split returns a Variant containing an array of strings. This is a straightforward way to capture the result in VBA without trying to declare a fixed-size array in advance. The number of pieces can vary with the input.
Syntax and Optional Arguments
The function takes a required expression and optional delimiter, limit, and comparison setting. The expression is the string to split. If you omit the delimiter, VBA uses a space. If you omit the limit, VBA returns all the pieces it can find. If you omit the comparison setting, the module’s Option Compare setting determines the comparison behavior.
- Delimiter: The exact character or string that marks each boundary. A delimiter can be more than one character, such as a line break sequence.
- Limit: The maximum number of pieces to return. The default value, -1, means return all pieces.
- Compare: Determines how VBA compares text when identifying the delimiter. Common choices are
vbBinaryCompareandvbTextCompare.
For example, with the text one|two|three, a pipe character delimiter returns three elements. Setting a limit of 2 returns one and two|three: after the first split, the remainder stays together as the last element. This is handy when the first delimiter separates a label from a value but the value may contain further delimiters.
Choosing a Delimiter and a Limit
Choose a delimiter that reliably separates fields in the source data. A comma works for simple comma-separated values, but not necessarily for a properly formatted CSV file: CSV can contain quoted fields with commas inside them, escaped quote characters, and line breaks within a field. Splitting such a file at every comma can break a single field into several incorrect pieces. For full CSV parsing, use a CSV-aware import method rather than assuming that every comma is a boundary.
When you need only an initial section and a remainder, use the limit argument. The following example returns two parts from a colon-delimited string. The text after the first colon remains intact in the second part.
Save a copy of the workbook before running this macro, as VBA actions cannot be reversed with Ctrl+Z.
Option Explicit
Sub SplitAtFirstColon()
Dim sourceText As String
Dim parts As Variant
' Keep everything after the first colon together.
sourceText = "Status: waiting: for review"
parts = Split(sourceText, ":", 2, vbBinaryCompare)
Debug.Print parts(0)
Debug.Print parts(1)
End Sub
Here, the first printed value is Status, and the second is waiting: for review, including the space that followed the first colon. Split does not automatically trim spaces around a field. If clean-up matters, remove unwanted leading or trailing spaces from each piece separately, for example with VBA’s Trim function.
Empty Fields, Spaces, and Line Breaks
Repeated delimiters produce empty elements; they are not silently collapsed. For instance, splitting red,,blue, at commas produces four elements: red, an empty string, blue, and another empty string. These empty pieces may represent missing values in the source, so removing them automatically could change the meaning or position of later fields.
Likewise, a space delimiter splits at each individual space. If the input contains several spaces in a row, the result can include empty strings between them. If the goal is to separate words regardless of how many spaces appear, normalize the text first or ignore empty pieces when processing. If the goal is to preserve the original field positions, keep the empty entries.
For line-oriented text, VBA provides constants such as vbCrLf for the Windows carriage-return-and-line-feed sequence and vbLf for a line-feed character. Match the delimiter to the actual text. Text copied from another system may use a different line-ending convention, so splitting on the wrong sequence can return the entire input as one piece.
An empty input string is a special case: the documented result is an empty array, rather than an array containing one ordinary non-empty field. Avoid assuming that a usable first or last index exists for empty input. If your code needs to handle that case, check the source string before splitting and decide what an empty input should mean for your task.
Comparison Behavior and Case
The comparison argument matters when the delimiter contains letters and you need to match it without regard to letter case. With a delimiter such as END, binary comparison treats differently cased text as different, while text comparison ignores case for the match. For punctuation-only delimiters such as commas, the distinction usually has no practical effect.
Use vbBinaryCompare when the delimiter should match exactly, or vbTextCompare when letter case should not matter. If you leave the argument out, VBA follows the module’s Option Compare declaration; when no declaration changes the default, binary comparison is used. Making the choice explicit can make code easier for another person to understand.
Common Problems and How to Avoid Them
- Starting at index 1: The first element is normally at index 0. Use
LBoundto obtain the lower bound instead of assuming it. - Using the wrong delimiter: If the delimiter does not occur in the text, the result is one element containing the original string. Check for tabs, line endings, or multi-character separators when a split appears not to work.
- Expecting spaces to disappear: The delimiter is removed, but spaces next to it remain. Trim each result if those spaces are unwanted.
- Dropping blank values: Consecutive or trailing delimiters can create empty elements. Decide whether to preserve, skip, or flag them before processing the array.
- Assuming every comma is a safe boundary: Quoted CSV fields can contain commas. Use a proper CSV import or parser when the input follows CSV rules.
- Using an array bound without checking input: Empty input can return an empty array. Handle that case before code tries to read an element.
For a normal nonempty result, LBound and UBound let a loop adapt to the actual array size. A For Each loop is another option when you only need to visit every value and do not need its numeric index. If you need to write values to worksheet columns, remember that VBA array indexes begin at zero while worksheet columns begin at one; calculate the destination column deliberately.
VBA Split Compared with Other Options
In Excel for Microsoft 365, the worksheet function TEXTSPLIT can split text directly into cells with a formula. That is often more convenient when the result should update automatically as a source cell changes. Split is better suited to macro logic, automation, or transformations that need to happen as part of a VBA procedure. The two functions have different syntax and run in different places.
Excel’s Text to Columns command is another practical choice for a one-time worksheet operation. It is available from the Data tab and guides you through selecting a delimiter and previewing the result. Unlike a VBA array, its primary result is divided worksheet cells. On Excel for the web, VBA macros cannot be created or run; use the desktop Excel app for this VBA approach. Excel for Mac supports VBA, although editor access and some macro capabilities can differ from Windows.
If the language you mean is VB.NET rather than Excel VBA, the general idea is similar, but the syntax and return type differ: .NET uses the string’s Split method and returns a string array. Do not paste VBA examples directly into a VB.NET project or assume that code written for one environment will compile unchanged in the other.
Frequently Asked Questions
Does VBA Split start its array at zero?
Yes. A normal result from Split is zero-based, so the first piece is at index 0. Use LBound and UBound when looping instead of hard-coding indexes.
How do I split a string by a comma in VBA?
Pass the string and a comma as the delimiter to Split, then store the returned array in a Variant variable. Loop through the result to inspect or use each field. Remember that a simple comma split is not a full CSV parser.
Can Split use more than one character as a delimiter?
Yes. The delimiter can be a string, not just a single character. It must match the sequence in the source text; for example, a Windows line ending can be handled with vbCrLf.
Why does Split return only one item?
The delimiter may not exactly match what is in the source, or it may not occur in the text. Check whether the source uses a tab, a different line ending, or a multi-character separator. If a limit is specified, it also restricts how many pieces are returned.
Does Split remove spaces around each result?
No. It removes the delimiter itself, not surrounding spaces. Apply trimming as a separate step when appropriate, and preserve whitespace when it is meaningful to the data.
Related
- Hide Column Headers in Power BI: Table & Matrix
- How to Calculate Mass Balance in Chemistry
- Excell Battery Corporation: What It Sells and How It Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Edit a Worksheet with VBA MacrosTo edit a worksheet with Excel VBA, write a macro that identifies the worksheet and changes its cells,…
- VBA If Not IsError: Meaning and ExamplesIn VBA, If Not IsError(value) Then means “run this code if value is not an error value.” Use…
- Macro Recorder for Mac OS X: Best Options and SetupFor a macro recorder on Mac OS X, use Automator’s “Watch Me Do” recording feature if your version…
- VBA Break Function: How to Pause or Stop a Running MacroVBA does not have a built-in Break function or statement. To interrupt a running macro, use the Visual…
- VBA Array Slices: How to Extract and Copy Array Parts SafelyVBA has no built-in array-slice syntax: to take a range of elements, copy them into a new array…
- Excel VBA on Mac: How to Open Files with a MacroYes. Excel for Mac can open a workbook from VBA with Workbooks.Open; give it the file’s full Mac…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.