Excel VBA 入门基础

 

Private Sub RegExp_Replace()

    Dim RegExp As Object
    Dim SearchRange As Range, Cell As Range
    
    '此处定义正则表达式
    Set RegExp = CreateObject("vbscript.regexp")
    RegExp.Pattern = "[0-9]{5}"
     
    '此处指定查找范围
    Set SearchRange = ActiveSheet.Range("A1:A99")
    
    '遍历查找范围内的单元格
    For Each Cell In SearchRange
        Set Matches = RegExp.Execute(Cell.Value)
        If Matches.Count >= 1 Then
            Set Match = Matches(0)'此处是冗余代码
            Cell.Value = RegExp.Replace(Cell.Value, "")'Cell.Value中所匹配
        End If
    Next

End Sub

Excel VBA 入门基础

EXCEL VBA 使用正则表达式清洗替换数据