如何在Excel VBA中按逗号加8位数字分割字符串？

The issue with your current code is that VBA's Split function works with literal string delimiters, not regular expressions. When you pass ",########" to Split, it’s looking for that exact sequence of characters (a comma followed by eight hash symbols), not a comma plus any 8-digit number—that’s why your array only contains the original full string.

To solve this, you need to use regular expressions to identify the segments you want directly. Here’s how to do it properly:

Solution Using VBA Regular Expressions #

We’ll use the RegExp object to find all valid segments (each starting with an 8-digit number followed by text, stopping at the next comma+8-digit sequence or the end of the string).

Late Binding (No Library Reference Needed)

This code is portable and doesn’t require setting a reference in the VBA editor:

Dim inputStr As String
inputStr = .Cells(i, j).Value2

' Create regex object
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "\d{8}\s.*?(?=,\d{8}|$)" ' Match 8 digits + space + text until next split point
regex.Global = True ' Find all matches, not just the first

' Execute regex to get matching segments
Dim matches As Object
Set matches = regex.Execute(inputStr)

' Populate the array with results
Dim arrCombined() As String
If matches.Count > 0 Then
    ReDim arrCombined(0 To matches.Count - 1)
    Dim k As Integer
    For k = 0 To matches.Count - 1
        arrCombined(k) = matches(k).Value
    Next k
End If

Early Binding (With IntelliSense Support)

If you want autocomplete and type checking, go to Tools > References in the VBA editor, check "Microsoft VBScript Regular Expressions 5.5", then use this code:

Dim inputStr As String
inputStr = .Cells(i, j).Value2

Dim regex As New RegExp
regex.Pattern = "\d{8}\s.*?(?=,\d{8}|$)"
regex.Global = True

Dim matches As MatchCollection
Set matches = regex.Execute(inputStr)

Dim arrCombined() As String
If matches.Count > 0 Then
    ReDim arrCombined(0 To matches.Count - 1)
    Dim k As Integer
    For k = 0 To matches.Count - 1
        arrCombined(k) = matches(k).Value
    Next k
End If

Regex Pattern Breakdown #

Let’s break down the pattern "\d{8}\s.*?(?=,\d{8}|$)":

• \d{8}: Matches exactly 8 consecutive digits
• \s: Matches the space immediately after the digits (use \s? if spaces are optional)
• .*?: Non-greedy match of any characters (stops at the first valid split point instead of the end of the string)
• (?=,\d{8}|$): Positive lookahead that stops the match when it hits either ",XXXXXXX" (comma + 8 digits) or the end of the string ($)

This will correctly split your input into the two desired segments and store them in arrCombined.

内容的提问来源于stack exchange，提问作者HYs