VBA Dictionary Split: Turn Text into Keys and Values

VBA & macros · Updated November 18, 2026

In VBA, use the Split function to break a string into an array, then add the pieces to a Scripting.Dictionary when you need unique keys, lookups, or counts. For example, splitting a list of words and using each word as a dictionary key lets you count how often it appears; the dictionary does not split text by itself.

What “VBA dictionary split” means

The phrase usually describes a two-step operation: Split separates text at a delimiter, and a dictionary stores the resulting pieces. A delimiter is the character or text that marks a boundary, such as a comma, a space, or an equals sign. The right approach depends on what the pieces represent.

If the pieces are labels that should occur only once, make them dictionary keys. If repeated pieces matter, store a count or other value against each key. If the input contains pairs such as color=blue, split the pair into a key and a value, then add that mapping to the dictionary. These are related tasks, but they are not interchangeable: a dictionary key must be unique, while an array returned by Split can contain duplicates.

In Excel for Microsoft 365 on Windows, Scripting.Dictionary is commonly used for this pattern. It holds key-and-item pairs and provides methods such as .Add, .Exists, and .Keys. It is an object, so create it with Set before using it.

How the Split function works

Split(expression, delimiter) returns an array of substrings. For example, Split("oak|maple|birch", "|") produces three elements: oak, maple, and birch. The delimiter is removed; it is not included in the resulting elements. By default, the array has a zero-based lower bound, so its positions are 0, 1, and 2 in this example.

You can pass an optional limit to cap how many pieces are returned, and an optional comparison setting to control matching of the delimiter. The limit is useful when you want to separate a first field from the remainder of a string. For instance, a limit of 2 when splitting name=first=last at = gives the first piece and the remaining text as the second piece. Without a limit, it produces three pieces.

One important boundary: Split accepts one delimiter string, not a list of alternative delimiters. Splitting on a space will not automatically split on commas or semicolons. Replace or normalize those characters first, or write a loop that handles each delimiter explicitly. An empty delimiter has special behavior and is not a way to split a string into individual characters; check VBA’s Split rules if that is the goal.

Store split parts as dictionary keys

To collect distinct parts, loop through the array and add a part only when the dictionary does not already contain it. The .Exists method prevents duplicate-key errors. Set .CompareMode before adding any keys if text matching should ignore letter case; changing it after entries have been added can raise an error.

Sub ListUniqueParts()
    Dim sourceText As String
    Dim parts As Variant
    Dim part As Variant
    Dim items As Object

    ' Create a dictionary using late binding.
    Set items = CreateObject("Scripting.Dictionary")
    items.CompareMode = vbTextCompare

    sourceText = "pear|plum|pear|apple"
    parts = Split(sourceText, "|")

    For Each part In parts
        If Len(Trim$(CStr(part))) > 0 Then
            If Not items.Exists(Trim$(CStr(part))) Then
                items.Add Trim$(CStr(part)), True
            End If
        End If
    Next part

    ' Print each unique key in the Immediate window.
    For Each part In items.Keys
        Debug.Print CStr(part)
    Next part
End Sub

This example leaves three keys: pear, plum, and apple. The associated item is set to True because only membership matters here; it could instead hold a description or another useful value. The trimming step removes spaces at the edges of a piece, and the empty-string check skips blank pieces. If spaces are meaningful in your data, do not trim them automatically.

The code uses late binding: items is declared as Object, and CreateObject creates the dictionary at runtime. On Windows, this avoids selecting a reference in the VBA editor. An alternative is to enable Microsoft Scripting Runtime under Tools > References and declare the variable as Scripting.Dictionary. Early binding offers editor autocomplete and compile-time type information, but the reference must be available on the computer running the workbook.

Count repeated words instead of discarding them

A common reason to combine splitting with a dictionary is to count repeated terms. Instead of adding a key only once, check whether it exists: increment its current item if it does, or add it with an initial count of 1 if it does not. In this example, common punctuation is changed to spaces before splitting, and case-insensitive matching treats Red and red as the same key.

Sub CountWordsFromText()
    Dim sourceText As String
    Dim normalizedText As String
    Dim parts As Variant
    Dim part As Variant
    Dim word As String
    Dim key As Variant
    Dim counts As Object

    ' Save a copy of the workbook before running any macro.
    Set counts = CreateObject("Scripting.Dictionary")
    counts.CompareMode = vbTextCompare

    sourceText = "Red, blue red; green."
    normalizedText = sourceText
    normalizedText = Replace(normalizedText, ",", " ")
    normalizedText = Replace(normalizedText, ";", " ")
    normalizedText = Replace(normalizedText, ".", " ")

    parts = Split(normalizedText, " ")
    For Each part In parts
        word = Trim$(CStr(part))
        If Len(word) > 0 Then
            If counts.Exists(word) Then
                counts(word) = CLng(counts(word)) + 1
            Else
                counts.Add word, 1
            End If
        End If
    Next part

    For Each key In counts.Keys
        Debug.Print CStr(key), counts(key)
    Next key
End Sub

The output represents three distinct words: red has a count of 2, while blue and green each have a count of 1. Punctuation handling here is deliberately limited to commas, semicolons, and periods; add replacements for other characters in your data, such as question marks or parentheses. This simple normalization also does not distinguish a decimal point from sentence punctuation, so it is not suitable for every kind of text.

Run a procedure from the VBA editor and inspect the Immediate window to see its Debug.Print output. In the editor, the Immediate window is available from View > Immediate Window. If a macro changes worksheet data, save a copy of the workbook first: VBA actions cannot be reversed with Ctrl+Z. A macro-enabled workbook is normally saved as an .xlsm file.

Split key-value pairs into dictionary entries

Another interpretation of “dictionary split” is parsing records such as region=West into a dictionary entry whose key is region and whose item is West. A limit of 2 helps preserve additional equals signs in the value. Validate the number of parts before accessing them, and decide what to do if a key is repeated.

Sub ParseOnePair()
    Dim record As String
    Dim fields As Variant
    Dim settings As Object

    ' Save a copy before running a macro that changes workbook data.
    record = "region=West=Coast"
    fields = Split(record, "=", 2)

    If UBound(fields) = 1 Then
        Set settings = CreateObject("Scripting.Dictionary")
        settings.Add Trim$(CStr(fields(0))), Trim$(CStr(fields(1)))
        Debug.Print settings("region")
    Else
        Debug.Print "Record has no key-value separator."
    End If
End Sub

With this input, the printed value is West=Coast. That is useful when the first equals sign marks the boundary but later ones belong to the value. If a record can contain an equals sign inside the key, this rule is not enough; the input format needs an escaping convention or a different delimiter. Also determine whether duplicate keys should replace an earlier value, be rejected, or be retained in a separate collection. Calling .Add for a duplicate key raises an error.

Choose a dictionary, array, or collection

A dictionary is a good fit when you need to retrieve an item by a unique key, test whether a key exists, or group repeated values. It is not automatically the best container for every split result. Choose based on how the result will be used:

  • Array: Keep every piece in its original sequence, including repeated entries. An array is a natural choice when you only need to loop through the parts.
  • Dictionary: Associate each unique key with an item, or use a key to count and group occurrences. It is convenient for lookups, but do not rely on dictionary key order as a substitute for an explicitly ordered list.
  • Collection: Keep a sequence of items and add or retrieve them by position. A collection can also use keys, but it does not provide the same built-in .Exists check as a dictionary.

For example, if you split a list of email addresses and need to retain every occurrence in the order entered, keep the array or copy the pieces into a collection. If the aim is to remove duplicates or count messages per address, a dictionary is more direct. For a small fixed set of values, a simple loop may be easier to understand than introducing a dictionary.

Common errors and data issues

Many problems come from treating the input as cleaner or more uniform than it is. A delimiter may be absent, a piece may be blank, or punctuation and inconsistent spaces may create unexpected keys. Consider these checks before relying on the result:

  • Leading, trailing, or repeated delimiters: A string such as |pear||plum| can produce empty elements. Skip them when they are not meaningful, or preserve them if an empty field represents real data.
  • Whitespace: pear and pear are different strings unless you trim the pieces. Trim only when surrounding spaces are formatting noise.
  • Case sensitivity: Set CompareMode to vbTextCompare before inserting keys for case-insensitive text matching. The default binary comparison distinguishes differently cased keys.
  • Duplicate keys: .Add fails if a key already exists. Use .Exists to choose whether to update the item, ignore the duplicate, or count it.
  • Delimiter inside a value: A basic split can break a field that legitimately contains the delimiter. A maximum split count helps in some formats, but quoted CSV fields require a CSV-aware parser; splitting on commas alone does not correctly handle every quoted comma.

For large inputs, dictionaries are often convenient because membership checks avoid repeatedly scanning a growing list, but actual speed depends on the work being done and the data. First make the parsing rules correct, then measure performance if a workbook is slow. Reading a worksheet range into an array, processing it in memory, and writing results back in batches can also reduce repeated worksheet interaction.

Windows and Mac compatibility

The examples use CreateObject("Scripting.Dictionary"), which is a common Windows VBA approach. Microsoft Scripting Runtime is a Windows component, so a workbook that depends on it should not be assumed to run unchanged in Excel for Mac. Mac VBA has differences in its support for external components; test the actual workbook on the target Mac version rather than relying on the Windows setup.

If the workbook must be portable between Windows and Mac, consider an array or a Collection when its features are sufficient, or use a dictionary implementation designed for cross-platform VBA. A collection does not offer a direct .Exists method, so code may need error handling or a separate lookup routine. Whichever option you choose, test duplicate handling, comparison rules, and the exact input delimiters on both platforms. Excel for the web does not run VBA macros, so this code must run in the desktop Excel application.

Frequently Asked Questions

Can I split a string directly into a VBA dictionary?
No. Split returns an array, not a dictionary. Loop through that array and add its elements to a dictionary as keys, values, or both.

How do I split text using commas and spaces?
Split takes one delimiter at a time. Replace selected separators with a common delimiter such as a space, then split on that delimiter, or write a parser that handles each separator. Normalize carefully if those characters can appear inside legitimate values.

How do I avoid duplicate-key errors?
Use .Exists(key) before calling .Add. If the key exists, update its item, skip it, or increment a count according to the intended result.

Does Split return a zero-based array?
By default, yes. Its first element is at index 0. A For Each loop avoids hard-coding array bounds when you simply need to process every returned element.

Can this code run in Excel for Mac or Excel for the web?
Do not assume the Windows Scripting.Dictionary example will work on Mac without testing or adapting it. Excel for the web does not execute VBA macros; use desktop Excel to run VBA.

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.