Yes. Microsoft Access’s VBA Split function divides a string into a zero-based array of smaller strings wherever it finds a chosen delimiter. For example, Split("Red,Green,Blue", ",") returns three elements: Red, Green, and Blue.
What the Access Split function does
Split is useful when one text value contains several pieces of information separated by a consistent character or string. In an Access application, you might use it in VBA to separate a comma-delimited list of tags, divide a code into parts, or process a value entered on a form. The result is an array, not a single text value. You can then refer to a particular element or loop through the elements.
For example, splitting "North|Central|South" using the vertical bar as the delimiter produces an array whose elements are North, Central, and South. The first element is at index 0, the second at index 1, and the third at index 2. That zero-based indexing matters: the first element is not at index 1.
The function belongs to Access VBA, the Visual Basic for Applications environment used to add code to an Access database. It is not the same as splitting an Access database into a front end and a back end, nor is it a command for dividing a form window. Those are separate Access features.
Syntax and arguments
The VBA syntax is Split(expression, [delimiter], [limit], [compare]). The square brackets indicate optional arguments; do not type the brackets when calling the function. expression is the string to divide. The other arguments control where it divides, how many elements it returns, and how it compares the delimiter.
expression: The text to split. It is required.delimiter: The text that marks a boundary between parts. If omitted, the default delimiter is a space.limit: The maximum number of substrings to return. If omitted, the default is -1, which returns all substrings.compare: The comparison setting used when matching the delimiter. If omitted, the module’sOption Comparesetting determines the comparison behavior.
For instance, Split("A/B/C", "/") uses a slash as the delimiter. A delimiter can contain more than one character: Split("one::two::three", "::") separates the text at each double colon. Choose a delimiter that is unambiguous in the data; if the delimiter can also appear within a value, a simple split may not be sufficient.
A basic VBA example
In Access for Windows, press Alt+F11 to open the Visual Basic Editor, then put VBA code in a standard module or in an appropriate form or report module. Access does not have a Mac desktop version, so there is no Mac equivalent to this Access-specific workflow. This example prints each part of a comma-separated string to the Immediate window, which you can display in the editor with Ctrl+G.
Option Explicit
Public Sub ShowParts()
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 Trim$(CStr(parts(i)))
Next i
End Sub
The call creates three elements. Trim$ removes the spaces at the start or end of each element, so the output is Red, Green, and Blue rather than values with leading spaces. LBound and UBound find the lowest and highest valid indexes. Using these bounds is safer than assuming a particular number of elements.
The code is a procedure, not a macro that updates records. If you adapt VBA to change data in a real database, make a backup copy first. Changes made by VBA generally cannot be reversed with Ctrl+Z.
Using the limit and comparison arguments
The limit argument is helpful when you want to split at only the first delimiter, rather than treating every delimiter as a boundary. For example, Split("item:details:extra", ":", 2) returns two elements: item and details:extra. This can be useful when the first part is a label and the remainder should stay together. With the default limit of -1, the same string would be divided into three elements.
The compare argument controls how VBA matches the delimiter. vbBinaryCompare performs a binary comparison, while vbTextCompare performs a text comparison that is not case-sensitive in the usual way. Access VBA also provides vbDatabaseCompare for comparison according to the database’s sort rules. For example, if a delimiter contains letters, the comparison setting can affect whether differently cased text matches it. If you omit this argument, check the module’s Option Compare statement rather than assuming a particular setting.
For common punctuation delimiters such as a comma or slash, the comparison choice usually makes no practical difference. It matters more when the delimiter contains alphabetic characters or when consistent text-matching behavior is important. If you need predictable behavior, specify a comparison constant explicitly and test it with representative data.
Handling spaces, repeated delimiters, and empty text
Split separates text; it does not clean or validate the values. If a string is "Red, Green", the second element begins with a space. Trim each element if surrounding whitespace should be ignored. Also decide how your application should treat empty elements. Consecutive delimiters, as in "A,,C", indicate an empty value between the two commas; leading or trailing delimiters can likewise leave an empty part at an edge.
An empty expression is a special case: Split returns an empty array, not an array containing an empty string. Code that immediately reads parts(0) or assumes UBound(parts) will succeed can therefore fail on empty input. Check the source before splitting, or write the calling procedure so it can safely handle no elements.
An empty delimiter is another special case. If the delimiter is a zero-length string, Split returns an array containing the original expression rather than dividing it character by character. If the delimiter does not occur in the expression, the result likewise contains the original text as one element. These behaviors are useful to know when a delimiter comes from a form control or another variable that might be blank.
When Split is not the right parser
A common misconception is that Split is a complete way to read CSV files. It is not. A basic split on commas cannot correctly interpret every CSV record: a field may contain a comma inside quotation marks, and quotation marks may have escaping rules. For example, splitting a record such as "Smith, Jane",42 at every comma would break the name into two pieces even though it is one quoted field. Use a CSV-aware import or parsing method when the format permits quoted delimiters or other CSV conventions.
Likewise, splitting a value stored in a table does not automatically turn the pieces into separate records or columns. Split only returns an array in VBA. If the data represents multiple related items, a normalized table with one item per record is often easier to query, filter, and maintain than a delimited list in one field. Split can help with a one-time import or with processing legacy data, but it does not replace good table design.
It is also not generally a drop-in calculated-field solution when you need one scalar result in a query. Since it returns an array, the usual approach is to call it from VBA, select the needed element there, and then use or store that value appropriately. For query logic, consider whether a string function such as InStr, Left, or Mid better matches the specific task.
Common errors and practical checks
Most problems come from assumptions about the input rather than from the function itself. Before relying on a split result, test typical values and unusual ones: blank strings, missing delimiters, extra delimiters, spaces around parts, and values that contain the delimiter as ordinary content. Confirm both the number of returned elements and their indexes.
- If an index produces a subscript error, check that the array has that element and remember that its first index is 0.
- If returned text has unwanted spaces, trim each element after splitting rather than expecting
Splitto remove whitespace. - If the result has too many parts, check whether the delimiter appears inside the data and whether a positive
limitwould fit the intended format. - If case affects matching, review the
compareargument and the module’sOption Comparesetting. - If a value may be empty, handle that case before attempting to access an array element.
For data imported from another system, do not assume every row follows the same pattern just because a few examples do. Validate expected part counts before using a particular index, and decide what the application should do with malformed values. Logging or reporting unexpected inputs can make import routines easier to troubleshoot than silently discarding parts.
Frequently Asked Questions
Does Access Split start counting at 0 or 1?
It returns a zero-based array. The first item is element 0, the second is element 1, and so on. Use LBound and UBound when looping instead of hard-coding the bounds.
What is the default delimiter in Split?
If you leave out the delimiter argument, the default is a space. For clarity and to avoid surprises, specify the delimiter explicitly whenever the data uses a separator such as a comma, slash, or pipe.
Can I use Split directly in an Access query?Split returns an array rather than one scalar value, so it is not a straightforward calculated-field function. Use it in VBA when you need to work with array elements, or choose a scalar string function if the query only needs one piece of text.
Does Split remove spaces around each part?
No. It divides the original string at the delimiter and retains other characters, including spaces. Apply Trim$ to each element if leading and trailing spaces should be removed.
Can Split reliably parse every CSV file?
No. Splitting on commas does not account for quoted fields that contain commas or other CSV escaping rules. Use a CSV-aware import or parser for files that follow those conventions.
Choosing a reliable approach
Use Split when the input is a predictable string with a clear delimiter and you want its parts in VBA. Specify the delimiter, account for zero-based indexes, trim whitespace when appropriate, and test empty or irregular input. For quoted CSV data, complex formats, or information that should be stored and queried as separate records, choose a parser or data structure designed for that job instead.
Related
- Balance of Payments Mind Map: Accounts and Flows
- Split a Part in Onshape: Steps, Uses, and Fixes
- Excel Goal Seek on Multiple Cells Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Tracking Hours in a Daily TimesheetFor a basic Excel timesheet, put a start time in B2 and an end time in C2, then…
- Excel String Functions and Operators ExplainedIn Excel, string functions work with text, while operators combine values or compare them. The main text-joining operator…
- Average VLOOKUP in Excel: How to Average Matching RowsTo average values for every row that matches a lookup value, use AVERAGEIF rather than VLOOKUP. For example,…
- Date Difference in Minutes in Excel: Formulas That WorkIf A2 contains a start date and time and B2 contains an end date and time, enter =(B2-A2)*1440…
- Excel Filter by Named Range: What Works and HowYes—but Excel’s standard Filter dropdown does not accept a named range as a direct filter condition. You can…
- How to Multiply Different Cells in Excel With a FormulaTo multiply different cells in Excel, select an empty cell, type a formula that starts with an equals…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.