Excel VBA编程，通配符，批量替换、筛选所有数字、字母、常见标点符号、中文字符 Excel2007版,用Excel VBA将某些字符全部替换...

VBA\u6279\u91cf\u66ff\u6362EXCEL\u91cc\u9762\u7684\u5185\u5bb9,\u6c42\u4ee3\u7801~~

\u8fd0\u884c\u4ee5\u4e0b\u5b8f\u5373\u53ef
Sub \u67e5\u627e\u4e0e\u66ff\u6362()
Dim Cz As String
Dim Th As String
Cz = InputBox("\u8bf7\u8f93\u5165\u88ab\u66ff\u6362\u7684\u5b57\u7b26")
Th = InputBox("\u8bf7\u8f93\u5165\u66ff\u6362\u7684\u5b57\u7b26")
Cells.Replace What:=Cz, Replacement:=Th, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

\u8fd9\u4e2a\u95ee\u9898\u7528VBA\u53cd\u800c\u9ebb\u70e6\u4e86\u3002
\u4f60\u76f4\u63a5\u7528
\u67e5\u627e\u66ff\u6362
\u529f\u80fd\u542f\u4e0d\u662f\u66f4\u7b80\u5355\u3002
\u5982\u679c\u5c31\u662f\u60f3\u7528VBA\uff0c\u5efa\u8bae\u4f60\u5f55\u5236\u4e00\u4e2a
\u67e5\u627e\u66ff\u6362
\u7684\u5b8f\uff0c\u7136\u540e\u5728VBA\u4e2d\uff0c\u628a\u76f8\u5e94\u7684\u5f15\u7528\u6761\u4ef6\u6362\u4e0b\uff0c\u5e76\u4e14\u590d\u5236\u8fd9\u4e2a\u4ee3\u7801\u5230\u540e\u9762\u5c31\u53ef\u4ee5\u4e86\u3002
\u9700\u8981\u66ff\u6362\u51e0\u4e2a\u5c31\u590d\u5236\u591a\u5c11\u904d\u3002
\u8fd9\u6837\u505a\u65b9\u4fbf\u7684\u591a\u3002

代码如下：(样本文件见附件)

Sub test()
    Dim regX As Object, s As String, i, j
    Set regX = CreateObject("vbscript.regeXP")
    For i = 1 To Cells(Rows.Count, 1).End(3).Row
        With regX
            .Global = True
            For j = 2 To 5
                Select Case j
                 Case 2
                    s = "[^\u4e00-\u9fa5]" '取汉字
                Case 3
                    s = "\D" '取数字
                Case 4
                    s = "[^a-zA-Z]" '取字母
                Case 5
                    s = "[\u4e00-\u9fa50-9a-zA-Z]" '取符号
                End Select
                .Pattern = s
                Cells(i, j) = .Replace(Cells(i, 1), "")
            Next j
        End With
    Next
End Sub

插入模块，复制粘贴代码。
Function RegExpTest(patrn, strng, Optional ByVal fgf As String = "")
Dim regEx, Match, Matches
Set regEx = CreateObject("vbScript.regexp")
regEx.Pattern = patrn
regEx.IgnoreCase = True
regEx.Global = True

Set Matches = regEx.Execute(strng)
For Each Match In Matches
RetStr = RetStr & fgf & Match
Next
RegExpTest = Mid(RetStr, Len(fgf) + 1)
End Function
提取文字，B1输入公式=RegExpTest("[一-龥]",$A2)
提取字母，C1输入公式=RegExpTest("[a-zA-Z]",$A2)
提取数字，D1输入公式=RegExpTest("\d",$A2)
提取符号，E1输入公式=RegExpTest("[^a-zA-Z0-9\u4e00-\u9fff\+]",$A2)

扩展阅读：vba编程必背50个程序 ... excel vba实例教程 ... excel vba实例详解 ... excel vba编程教程完整版 ... vba编程从入门到精通教程 ... 最简单的宏程序编程 ... vba编程入门自学100例 ... vba编程代码对照表 ... 表格vba编程怎样使用 ...