Excel VBA UDF移除字符串特定字符时多余空格处理问题咨询

核心调整逻辑 #

• 放弃原全量替换特殊字符的逻辑，改为按数据组拆分后分别处理前缀、括号内容，避免误删组间分隔空格
• 前缀部分仅保留数字、大写英文字母，自动过滤内部空格，解决编码内部空格（如4 A）问题
• 括号内仅提取数字字符，自动过滤文本、空格、逗号等无关内容，解决括号内多余字符问题
• 所有处理完成的分组最终用单个空格拼接，保证仅保留组间分隔空格

完整代码 #

Function RemoveSpecial(Str As String) As String
    Dim regEx As Object
    Dim matchGroups As Object
    Dim parts As Object
    Dim group As String
    Dim prefix As String, numPart As String
    Dim resultArr() As String
    Dim i As Integer, j As Integer
    Dim c As String
    
    ' 晚绑定创建正则对象，无需手动添加库引用
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Global = True
    regEx.IgnoreCase = True
    
    ' 匹配所有独立数据组，按; , 拆分
    regEx.Pattern = "([^;,]+)[;,]?"
    Set matchGroups = regEx.Execute(Str)
    
    ReDim resultArr(0 To matchGroups.Count - 1)
    
    For i = 0 To matchGroups.Count - 1
        group = Trim(matchGroups(i).SubMatches(0))
        If group = "" Then GoTo NextGroup
        
        ' 拆分前缀与括号内内容
        regEx.Pattern = "^(.*?)\((.*)\)$"
        If regEx.Test(group) Then
            Set parts = regEx.Execute(group)(0).SubMatches
            ' 处理前缀：仅保留数字和大写字母，过滤所有空格
            prefix = ""
            For j = 1 To Len(parts(0))
                c = UCase(Mid(parts(0), j, 1))
                If (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z") Then
                    prefix = prefix & c
                End If
            Next
            ' 处理括号内容：仅保留数字
            numPart = ""
            For j = 1 To Len(parts(1))
                c = Mid(parts(1), j, 1)
                If c >= "0" And c <= "9" Then
                    numPart = numPart & c
                End If
            Next
            resultArr(i) = prefix & numPart
        End If
NextGroup:
    Next i
    
    ' 用单个空格拼接所有处理后的分组
    RemoveSpecial = Join(resultArr, " ")
End Function

场景验证效果 #

• 编码与括号间存在空格：输入4A (4,5,6,7,8,9); → 输出4A456789
• 括号内存在文本及空格：输入4A (4,5, skip 8,9); → 输出4A4589
• 编码内部存在空格：输入4 A(4,5,6) → 输出4A456
• 混合异常场景：输入4A (4,5,6,7,8,9); 4B(4,5,7,8); 3 A(1, skip 2,3); 3C(1,2) → 输出4A456789 4B4578 3A123 3C12

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