Use VBA’s built-in Split function to divide a string into a zero-based array: parts = Split(text, delimiter). For example, Split("red,green,blue", ",") returns three items separated at each comma.
What Split Does in VBA
Split searches a string for a delimiter and returns the pieces between matches in a one-dimensional array held in a Variant. It is useful when a value contains a consistent separator, such as commas in a simple list, pipes between fields, or line breaks between lines. You can then inspect each piece, assign it to a variable, or use it in other VBA operations.
The function divides text; it does not automatically place the pieces into worksheet cells, remove extra spaces, convert numbers into numeric values, or validate that the input follows a particular format. Those tasks require additional code. Keeping these responsibilities separate makes parsing easier to understand and troubleshoot.
Syntax and Arguments
The full syntax is Split(expression, [delimiter], [limit], [compare]). The first argument is the string to divide. The other arguments are optional:
expressionis the source string.delimiteris the text that marks where to split. If omitted, VBA uses a space character.limitsets the maximum number of substrings to return. If omitted, or set to -1, all matches are used.comparecontrols how VBA compares the delimiter with the source text. The default isvbBinaryCompare.
For ordinary cases, the first two arguments are enough. Specify the delimiter rather than relying on the default: an omitted delimiter splits on spaces, which may not be the separator in your data. The function returns a Variant containing an array, so declare the receiving variable as Variant, not as a single String.
A Basic VBA Example
This macro splits a comma-separated string and prints each piece in the Visual Basic Editor’s Immediate window. The index starts at zero, which is why the output loop uses LBound and UBound rather than assuming a one-based array.
Sub ShowSplitParts()
Dim sourceText As String
Dim parts As Variant
Dim i As Long
sourceText = "red,green,blue"
parts = Split(sourceText, ",")
For i = LBound(parts) To UBound(parts)
Debug.Print i, parts(i)
Next i
End Sub
To try it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the procedure there. The Immediate window is available from the editor’s View menu. On a Mac, the Visual Basic Editor can be opened with Option+F11 on many keyboards; depending on the keyboard and macOS settings, you may need to use the Fn key as well. Before running any macro, save a copy of the workbook: macro changes cannot be undone with Ctrl+Z. Save a workbook containing VBA as a macro-enabled .xlsm file if you want to retain the code.
Choosing a Delimiter
A delimiter can be one character or a sequence of characters. Common choices include a comma (","), a pipe ("|"), a tab (vbTab), or a line break such as vbCrLf. Match the delimiter to the actual data. A comma and a comma followed by a space are different delimiters: if you split on just the comma, the next item may begin with a space.
For example, this procedure separates a pipe-delimited value and trims spaces at the edges of each token before printing it:
Sub ShowTrimmedParts()
Dim sourceText As String
Dim parts As Variant
Dim token As Variant
sourceText = "North | South | West"
parts = Split(sourceText, "|")
For Each token In parts
Debug.Print Trim$(CStr(token))
Next token
End Sub
Trim$ removes ordinary leading and trailing spaces; it does not remove every possible whitespace character. If the input uses tabs or unusual spacing, handle those characters explicitly. Trimming each result is often safer than making the delimiter a comma-plus-space, because it also handles inconsistent spacing around the separator.
Working with the Returned Array
In VBA, arrays created by Split are zero-based. For a nonempty source string, a typical loop is For i = LBound(parts) To UBound(parts). This is more robust than writing fixed bounds such as 0 to 2, since the number of pieces can change with the input. You can also use For Each when you need each value but do not need its position.
To put each part into a worksheet cell, loop over the array and write to a range. The following example places the pieces from a single source string across row 1, starting in column A:
Sub WritePartsAcrossRow()
Dim sourceText As String
Dim parts As Variant
Dim i As Long
sourceText = "red,green,blue"
parts = Split(sourceText, ",")
For i = LBound(parts) To UBound(parts)
Cells(1, i - LBound(parts) + 1).Value = parts(i)
Next i
End Sub
This example uses the active worksheet because it does not qualify Cells with a worksheet variable. In a larger macro, explicitly refer to the intended worksheet, such as a worksheet stored in a variable, to avoid writing to whichever sheet happens to be active. If you want the results down a column instead, use the array index to calculate the row and keep the column fixed.
Using Limit and Compare
The optional limit argument is helpful when only the first separator matters. For example, splitting a setting such as Theme=Dark=HighContrast with a limit of 2 produces a key and a remaining value: Theme and Dark=HighContrast. Without the limit, VBA would return three pieces. A limit of 1 returns the whole expression as one piece, because no more than one substring is requested.
The compare argument matters when the delimiter contains letters and its capitalization may differ from the source. vbBinaryCompare compares characters according to their binary values and is the default. vbTextCompare performs a text comparison, so delimiter matching is not case-sensitive. Other comparison constants include vbUseCompareOption; vbDatabaseCompare is intended for Access database comparisons. For punctuation delimiters such as commas, the default comparison is normally sufficient.
For example, to split at either uppercase or lowercase occurrences of the letter X, use Split(sourceText, "x", -1, vbTextCompare). Choose text comparison deliberately: it can match a delimiter with different letter case, but it does not make unrelated formatting differences disappear.
Empty Values and Consecutive Delimiters
Input quality affects the array you get. If two delimiters appear next to each other, the text between them is an empty string. A value such as "A,,C" therefore has an empty middle item rather than silently losing that position. A delimiter at the end can also leave an empty final field. That behavior is useful when empty fields have meaning, but it may surprise code that assumes every item contains text.
An empty source string is a special case: Split returns an empty array. Do not blindly call LBound or UBound and loop on data that might be empty. Check the input first if an empty string is possible, and decide what your procedure should do in that case. Also distinguish an empty string from a string containing spaces; the latter is not empty, even if trimming it would leave no visible characters.
When blank fields are not valid, test each token after splitting and report or skip the blank value according to the requirements. Do not remove empty items automatically if their positions correspond to real columns or fields in the source data.
Common Parsing Problems and CSV Limits
A frequent mistake is using Split as though it were a complete CSV parser. It is not. A comma in a quoted CSV field may be part of the field rather than a separator. For example, a row containing "Smith, Jane",42 cannot be parsed correctly by splitting at every comma: the name would be broken into two pieces. Quoted fields can also contain escaped quotation marks and line breaks, which require additional parsing rules.
Use Split for simple, controlled text where the delimiter cannot occur inside a value, or where the data format explicitly treats every occurrence as a separator. For genuine CSV files with quoted fields, use an import method that understands CSV quoting, such as Excel’s text/CSV import tools, or write a parser that follows the file’s quoting rules. Another common error is assuming the delimiter is always present. If the source has no match, the result is still a one-item array containing the original text; check the number of pieces if your code requires a particular field count.
Finally, splitting does not convert text like "42" into a number. Convert values only after checking that the text is valid for the expected type, especially when the data may contain blanks, currency symbols, or locale-specific decimal separators.
VBA Split Compared with Excel TEXTSPLIT
Excel’s worksheet function TEXTSPLIT is a related option in Microsoft 365 and Excel 2024. It returns split values into the worksheet grid as a dynamic array, while VBA’s Split returns an array for use inside code. Choose VBA when a macro needs to parse text as part of a larger task, such as processing files or applying conditional logic. Choose TEXTSPLIT when you want a worksheet formula to display the results and your Excel version supports that function.
They are not interchangeable in every situation. VBA code runs through the Visual Basic for Applications environment, while a worksheet formula recalculates as cell values change. Also, worksheet formulas do not use VBA’s optional limit and compare arguments in the same way. If you are following VBA instructions, use Split; typing it into a worksheet cell does not call the VBA function as a normal worksheet formula.
Frequently Asked Questions
Does VBA Split start at zero or one?
The array returned by Split is zero-based. The first item is at index 0. Use LBound and UBound to get the actual bounds when looping, rather than assuming an index range for every possible input.
How do I split a string by a space in VBA?
Use Split(sourceText, " ") to split at ordinary space characters. You can also omit the delimiter because a space is the default, but specifying it makes the code clearer. Repeated spaces can create empty pieces, so consider normalizing or checking the input if that matters.
How do I split on a line break?
Use the line-break constant that matches the text. Windows text commonly uses vbCrLf, while some sources use vbLf. If you do not know which line ending is present, inspect or normalize the source first; a delimiter only matches the characters actually in the string.
Can Split return only the first two fields?
Yes. Set the third argument, limit, to 2, as in Split(sourceText, "=", 2). The second returned item contains the rest of the source after the first matching delimiter, including any later occurrences.
Why does Split leave spaces around my results?
The function separates text at the delimiter but does not trim the pieces. If the source has spaces around separators, loop through the array and apply Trim$ to each item, or otherwise clean the values according to the input format.
Related
- Assembly Split in CATIA: How to Separate a Product
- Balance Sheet Accounting: How It Works
- J Curve Balance of Payments: 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: Open the Latest Downloaded File AutomaticallyYes. In Excel for Microsoft 365 on Windows, a VBA macro can scan your Downloads folder, identify the…
- VBA Error Handling Tutorial: Errors, Handlers, and CleanupVBA error handling lets a macro respond to a runtime problem instead of stopping unexpectedly. Start with On…
- Excel VBA Find and Replace in a Range: Code and TipsTo find and replace text only within a chosen Excel range, use VBA’s Range.Replace method and call it…
- VBA UserForm: How to Show It in ExcelTo show a VBA UserForm in Excel, call its name followed by .Show, such as UserForm1.Show. Add vbModeless…
- VBA InputBox Integer: Get and Validate Whole NumbersVBA’s standard InputBox returns text, not an Integer. In Excel VBA, use Application.InputBox with Type:=1 to request a…
- VBA Create Word Document From TemplateTo create a Word document from a template with VBA, call Word’s Documents.Add method with the template’s full…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.