MS Access Split String: VBA Syntax and Examples

VBA & macros · Updated November 22, 2026

Yes. In Microsoft Access, VBA’s Split function breaks a string into a zero-based array of pieces using a delimiter such as a comma or space. For example, Split("North,South,East", ",") returns three elements; use an index such as parts(0) to read the first one.

What the Access Split function does

Split searches a string for a delimiter and separates the text wherever it finds that delimiter. The delimiter is removed from the returned pieces. The result is a Variant containing an array, not a single string and not an Access table. That distinction matters: you can work with the array in VBA, but a query cannot treat the result as though it were a set of rows without additional logic.

For instance, splitting "red|green|blue" on the pipe character produces the values "red", "green", and "blue". Array indexing starts at zero, so the first value is at index 0, the second at 1, and the third at 2. This remains true even if the VBA module uses Option Base 1; Split returns a zero-based array.

Split is useful for parsing a value that contains several pieces of text in a predictable format: a comma-separated list, a path separated by backslashes, or a code with a hyphen between sections. It is not a substitute for designing a database with related records when the pieces represent repeatable data.

Syntax and optional arguments

The VBA syntax is Split(expression, [delimiter], [limit], [compare]). The expression is the string to divide. If you omit the delimiter, Access uses a space. In most practical code, specify it explicitly so the result does not depend on an assumed separator.

  • Delimiter: The text that marks each boundary. It can be more than one character, such as "--".
  • Limit: The maximum number of pieces to return. The default, -1, means return all pieces. A positive limit restricts the number of returned pieces; use it only when you have a reason to leave part of the source together.
  • Compare: Controls text comparison when searching for the delimiter. The default is binary comparison. vbTextCompare requests text comparison, which can matter when the delimiter contains letters whose case may vary.

For a one-character separator such as a comma, the comparison option usually makes no visible difference. For a letter delimiter, such as splitting on “x,” comparison settings can affect whether uppercase “X” is treated as the same delimiter. The option applies to finding the delimiter; it does not change the case of the resulting text.

Basic VBA example

In Access for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then place this procedure in a standard module. A standard module is important if you later want to call a public function from an Access query.

Public Sub ShowSplitExample()
    Dim parts As Variant

    ' Split the text at each comma.
    parts = Split("North,South,East", ",")

    Debug.Print parts(0)  ' North
    Debug.Print parts(1)  ' South
    Debug.Print parts(2)  ' East
End Sub

Run the procedure from the Visual Basic Editor with the cursor inside it and press F5. Open the Immediate window with Ctrl+G to see the printed results. The Mac version of Microsoft Access is not available, so these Access VBA editor instructions and Windows shortcuts apply to the desktop application on Windows.

The sample only prints values; it does not change table data. If you adapt VBA to update or delete records, save a separate copy of the database before running it. Changes made by a macro or VBA procedure generally cannot be reversed with Ctrl+Z.

Getting a particular piece safely

Before reading an element, check that the requested index exists. A value may contain fewer delimiters than expected, and trying to access a missing array element causes a subscript-out-of-range error. This public function returns Null if the source is Null or the requested zero-based position is unavailable:

Public Function PartAt(ByVal sourceText As Variant, _
                       ByVal separator As String, _
                       ByVal zeroBasedIndex As Long) As Variant
    Dim pieces As Variant

    ' Keep Null input as Null rather than passing it to Split.
    If IsNull(sourceText) Then
        PartAt = Null
        Exit Function
    End If

    pieces = Split(CStr(sourceText), separator)

    If zeroBasedIndex < LBound(pieces) Or _
       zeroBasedIndex > UBound(pieces) Then
        PartAt = Null
    Else
        PartAt = pieces(zeroBasedIndex)
    End If
End Function

For example, calling PartAt("A-17-Blue", "-", 1) returns "17". The requested index is zero-based, so index 1 means the second piece. This helper returns Null for a missing position, which can be useful in a query or form; if you would rather return an empty string or a message, change the assignment in the out-of-range branch.

Using Split in an Access query

Access SQL does not provide a native SQL expression that directly returns an array and lets a query index it. However, a query can call a public VBA function in a standard module. After adding PartAt above, a query calculated field can use an expression such as SecondPiece: PartAt([CodeText], "-", 1). If CodeText contains AB-17-Blue, the calculated field returns 17.

To try it, create or open a query in Design View, add the table containing CodeText, and enter the expression in an empty Field row. The function name and argument order must match the VBA declaration. If Access displays an Enter Parameter Value box for PartAt, check that the function is public, is in a standard module rather than a form or report module, and that the spelling and field name are correct.

A query expression is convenient for displaying or exporting one known component. It is less suitable for turning every part of every record into a separate row, especially for large tables. Calling a VBA function for each record can also be slower than working with properly structured data, and the database must allow its VBA code to run. Do not assume that a query field containing a delimited list automatically becomes a relational child table.

Nulls, empty values, and repeated delimiters

Null is not the same as an empty string. Passing Null directly to Split can cause a VBA error, so test it with IsNull first, as the helper function does. If a field can be Null and you deliberately want to treat Null as empty text, you can use Nz([CodeText], "") in an Access expression. Decide which behavior is meaningful for your data rather than silently converting missing information into a blank component.

Repeated delimiters create empty elements between them. For example, splitting "A,,C" on a comma gives three pieces: "A", an empty string, and "C". A comma at the beginning or end of a value likewise leaves an empty first or last piece. Do not assume that Split discards blanks; if empty pieces should be ignored, add explicit logic to check each returned element.

Leading or trailing spaces are also retained. Splitting "A, B" on a comma returns a second piece with a leading space. You can trim an individual value with VBA’s Trim function, or clean the source when appropriate. Be cautious about trimming when spaces are significant, such as in a quoted address or a deliberately formatted field.

Choosing a delimiter and handling complex text

Split works best when the delimiter is unambiguous and cannot appear inside a piece of data. A pipe, tab, or a multi-character marker can be safer than a comma when the values themselves may contain commas. If the delimiter can occur as ordinary data, a simple Split cannot know whether that occurrence marks a boundary or belongs to the value.

For example, splitting "Doe, Jane,Sales" at every comma produces three pieces, even if the intended data was the name "Doe, Jane" and department "Sales". Proper CSV files can also use quotation marks and escaping rules; VBA’s basic Split function does not parse those CSV rules. For real CSV imports, use Access’s import tools or a parser designed to honor quoted fields rather than treating every comma as a separator.

When parsing a simple fixed pattern, check the input before trusting a particular index. A product code might normally contain two hyphens, but a malformed or newly formatted value may not. Validate the piece count and, where needed, check that the component has the expected form. That prevents a query or procedure from returning plausible-looking but incorrect results.

Split versus database normalization

A field such as "red,green,blue" is convenient for display, but it combines several values in one column. Searching for one whole value, enforcing uniqueness, editing one item, or relating each item to another table becomes harder. A normalized Access design generally stores each item in a separate row in a related table, with a foreign key pointing back to its parent record.

Use Split when the value is an external input, a temporary parsing task, or a fixed-format string that must be interpreted. If users routinely add, remove, search, or report on each contained item, consider changing the schema instead. A one-time parsing routine can split existing text and append the results into a related table, but it should include validation and be tested on a copy of the database before production data is changed.

Common errors and troubleshooting

  • Subscript out of range: The code asked for an index the returned array does not contain. Check the array bounds or use a helper such as PartAt.
  • Invalid use of Null: The source value was Null. Test it with IsNull or deliberately convert it with Nz before calling Split.
  • Unexpected spaces or blank pieces: Split preserves them. Inspect the source, trim pieces when appropriate, and decide how empty values should be handled.
  • Function not found in a query: Make the function Public and put it in a standard module. Also verify that VBA is enabled and that the function name is not misspelled.
  • Unexpected split points: The chosen delimiter appears within the data. Use a safer format or a parser that understands the data’s quoting and escape rules.

For a quick diagnostic, print the array’s bounds and each element in the Immediate window. This reveals whether the problem is the delimiter, the index, blank elements, or the actual input string. When the source comes from a table, test representative records, including Nulls and malformed values, not just an ideal example.

Frequently Asked Questions

Does Microsoft Access have a Split function?
Yes. VBA provides Split, which returns a zero-based array. Access SQL does not itself expose that array as a list of rows, though a query can call a public VBA function that returns one selected piece.

How do I get the first value after splitting?
Assign the result to a Variant array, then read element 0, for example parts = Split("A-B-C", "-") followed by parts(0). Check that the input is not Null before calling Split.

Can I split a field directly in a query?
Use a public function in a standard VBA module, such as PartAt([MyField], "-", 0), as a calculated query field. Split itself returns an array, so the query needs a function that selects and returns the desired component.

Does Split remove spaces or empty values?
No. It preserves spaces around each piece and returns empty pieces for repeated, leading, or trailing delimiters. Apply trimming or filtering separately if that matches the data rules.

Can Split correctly read every CSV file?
No. Basic Split separates at each delimiter and does not account for commas protected by quotation marks or other CSV escaping rules. Use Access’s import tools or a CSV-aware parser for complex CSV data.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.