Excel VBA单元格字符串处理异常：去空格多余逗号后截断错误

问题背景 #

Excel工作表"table"的H1单元格中，有由空格、逗号分隔的多段短字符串，需要移除所有空格、合并多余逗号，最终仅在各元素间保留单个逗号。现有VBA代码执行无报错，但字符串会被错误截断。

用户提供的代码 #

Sub String_adaption()

Dim i, j, k, m As Long
Dim STR_A As String

STR_A = "01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

i = 1

With Worksheets("table")

  For m = 1 To Len(.Range("H" & i))
      
      j = 1
      
    Do While Mid(.Range("H" & i), m, 1) = "," And Mid(.Range("H" & i), m - 1, 1) <> Mid(STR_A, j, 1) And m <> Len(.Range("H" & i))

            .Range("H" & i) = Mid(.Range("H" & i), 1, m - 2) & Mid(.Range("H" & i), m, Len(.Range("H" & i)))

            j = j + 1
    Loop
             
  Next m

End With

End Sub

问题原因分析 #

1. 动态字符串长度导致遍历错位：
   循环For m = 1 To Len(.Range("H" & i))基于原字符串长度初始化，但循环中直接修改单元格内容，字符串长度实时变化，后续m值无法匹配实际字符位置，最终引发错误截断。
2. 判断逻辑完全错误：
   Do While中的条件Mid(.Range("H" & i), m - 1, 1) <> Mid(STR_A, j, 1)是逐个对比STR_A的字符，只要有一个字符不匹配就执行删除操作，且j持续累加直到遍历完STR_A，这会错误删除大量正常内容。
3. 未处理空格需求：代码全程未对空格做任何判断或替换，完全没满足“移除所有空格”的要求。
4. 边界值错误：当m=1时，Mid(.Range("H" & i), m - 1, 1)会取Mid(..., 0, 1)，这是无效的空值，会导致条件判断逻辑混乱。

修复方案：正则表达式实现 #

用正则表达式可以简洁高效地完成需求，一次性处理空格、连续逗号、首尾多余逗号：

Sub String_adaption()
    Dim targetCell As Range
    Dim regExp As Object
    Set regExp = CreateObject("VBScript.RegExp")
    
    ' 配置正则规则
    With regExp
        .Global = True ' 全局匹配
        .IgnoreCase = False ' 区分大小写（这里不需要，但保持默认）
    End With
    
    Set targetCell = Worksheets("table").Range("H1")
    Dim tempStr As String
    tempStr = targetCell.Value
    
    ' 1. 移除所有空格
    regExp.Pattern = "\s+"
    tempStr = regExp.Replace(tempStr, "")
    
    ' 2. 把连续的逗号替换成单个逗号
    regExp.Pattern = ",+"
    tempStr = regExp.Replace(tempStr, ",")
    
    ' 3. 移除开头或结尾的逗号（如果存在）
    regExp.Pattern = "^,|,$"
    tempStr = regExp.Replace(tempStr, "")
    
    ' 写回单元格
    targetCell.Value = tempStr
End Sub

方案说明 #

• 先移除所有空格：用\s+匹配任意数量的空白字符，替换为空。
• 合并连续逗号：用,+匹配一个或多个连续逗号，替换为单个逗号。
• 清理首尾逗号：用^,|,$匹配开头或结尾的逗号，替换为空。

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