VBA正则清理字符遇格式问题：删除/时卡顿及相关技术疑问

在将一个.xlsx文件的部分信息导入另一表格时，遇到数据含多余字符的问题。已在VBA编辑器中启用Microsoft VBScript Regular Expressions 5.5，编写循环代码清理字符：

'remove special characters on the temporary sheet
Set SrchSht = SdsFile.Sheets("New IO")      'set sheet to search
Set SrchStrt = SrchSht.Cells(1, 1)          'start of search
Set SrchEnd = SrchSht.Cells(RowLim, ColLim) 'end of search
Set SrchRange = SrchSht.Range(SrchStrt.Address & ":" & SrchEnd.Address)       'full search range
'RegExSrchPat = "[^\.\w\(\) ,-:]+"      'match one or more characters not [^] in the brackets
RegExSrchPat = "[¶/]+"      'match one or more pillcrows or forward slashes
RegExRepPat = "" 
For Each C In SrchRange
    If RegExSrchPat <> "" Then
        SrchString = CStr(C.Value)
        With RegEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = True
            .Pattern = RegExSrchPat
        End With
        If RegEx.Test(SrchString) Then
            C.Value = RegEx.Replace(SrchString, RegExRepPat) 
        End If
    End If
Next

其中RowLim和ColLim分别设为3899和12。使用排除型正则模式[^\.\w\(\) ,-:]+时，正斜杠未被删除；改用匹配¶和/的[¶/]+模式时，前2435行正常处理，但A2436单元格值为=+MA-30509PL:1/PE时触发Run-time error '1004'，将该单元格格式改为文本后脚本恢复运行。

• 排除型正则为何未移除正斜杠？
• 匹配型正则为何引发错误？
• 如何批量将SrchRange单元格转为文本格式？
• 有无更优方案？

1. 排除型正则未移除正斜杠的原因 #

你的排除模式[^\.\w\(\) ,-:]+里，-放在了,和:之间——正则字符组中的-若不放在开头、结尾或转义，会被解析为范围运算符。这里,-:代表ASCII码从逗号（,，ASCII44）到冒号（:，ASCII58）之间的所有字符，而正斜杠/的ASCII码是47，正好落在这个范围内，所以被归为"允许保留"的字符，自然不会被匹配删除。

修正方法：把-移到字符组末尾，或者转义它，比如改成[^\.\w\(\) ,:-]+或[^\.\w\(\) ,\-:]+，这样-会被当作普通字符，不再形成范围，正斜杠就会被排除在外，从而被匹配删除。

2. 匹配型正则引发1004错误的原因 #

A2436单元格的值=+MA-30509PL:1/PE是Excel公式（以=开头）。当你用C.Value获取内容时，Excel会尝试计算这个公式，而1/PE会被解析为除法运算，但PE不是有效单元格引用或数值，导致公式计算错误；当你把替换后的结果写回单元格时，Excel仍会将其当作公式处理，最终抛出1004错误。

改成文本格式后，Excel不再将其解析为公式，而是当作普通字符串处理，脚本因此能正常运行。

3. 批量将SrchRange转为文本格式的方法 #

可以直接对整个范围设置格式，同时避免公式被错误计算：

' 先将范围设置为文本格式
SrchRange.NumberFormat = "@"
' 将单元格内容转为纯文本（保留原始公式文本而非计算结果）
SrchRange.Value = SrchRange.Value2

如果需要单独处理公式单元格，也可以用循环：

SrchRange.NumberFormat = "@"
For Each C In SrchRange
    If C.HasFormula Then
        C.Value = C.Formula
    End If
Next

4. 更优方案 #

方案一：优化正则与公式处理逻辑 #

在处理前判断单元格是否为公式，直接获取公式文本而非计算值，同时修正正则模式：

Set SrchSht = SdsFile.Sheets("New IO")
Set SrchRange = SrchSht.Cells(1, 1).Resize(RowLim, ColLim) ' 更简洁的范围定义
RegExSrchPat = "[^\.\w\(\) ,:-]+" ' 修正后的排除模式
RegExRepPat = ""

With RegEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = RegExSrchPat
End With

For Each C In SrchRange
    If RegExSrchPat <> "" Then
        ' 根据是否为公式选择获取内容的方式
        SrchString = IIf(C.HasFormula, C.Formula, CStr(C.Value))
        If RegEx.Test(SrchString) Then
            C.NumberFormat = "@"
            C.Value = RegEx.Replace(SrchString, RegExRepPat)
        End If
    End If
Next

方案二：用Excel内置函数替代正则（简单场景） #

如果仅需删除¶和/，可以用SUBSTITUTE函数批量处理，无需启用正则库，效率更高：

SrchRange.NumberFormat = "@"
SrchRange.Value = SrchSht.Evaluate("SUBSTITUTE(SUBSTITUTE(" & SrchRange.Address & ",""¶"",""""),""/"","""")")

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