EXCEL扩展API使用手册

EXCEL扩展API使用手册

一、使用说明：

1、 打开EXCEL。
2、 右键点击SHEET1，选择“查看代码”，如下图

3、 点击“工具栏”中的“模块”，如下图：

4、 点击左侧“模块1”，打开后，将API代码粘贴至“模块1”内。

二、API代码：

Function FIND_LIST(find_str, list)
    For Each mylist In list
        If InStr(find_str, mylist) > 0 Then
            FIND_LIST = mylist
            Debug.Print mylist
            Exit Function
        End If
    Next mylist
End Function
'超级清洗字符串函数
Function SUCLEAN(cell_text As Range, clean_str As String)
    Debug.Print clean_str
    Dim Cleaned As String
    Select Case clean_str
        Case "汉字"
            Cleaned = CleanRegExpStr(cell_text, "[\u4e00-\u9fa5]", True, True)
        Case "数字"
            Cleaned = CleanRegExpStr(cell_text, "[0-9]", True, True)
        Case "字母"
            Cleaned = CleanRegExpStr(cell_text, "[a-zA-Z]", True, True)
        Case "特殊字符"
            Cleaned = CleanRegExpStr(cell_text, "[^%&',;=?${}<>\x22]", True, True)
        Case Else
            Cleaned = CleanRegExpStr(cell_text, clean_str, True, True)
    End Select
    SUCLEAN = Cleaned
End Function
'超级挑捡字符串函数
Function SUPICK(cell_text As Range, pick_str As String)
    Debug.Print pick_str
    Dim Picked As String
    Select Case pick_str
        Case "汉字"
            Picked = PickRegExpStr(cell_text, "[\u4e00-\u9fa5]", True, True)
        Case "数字"
            Picked = PickRegExpStr(cell_text, "[0-9]", True, True)
        Case "字母"
            Picked = PickRegExpStr(cell_text, "[a-zA-Z]", True, True)
        Case "手机号"
            Picked = PickRegExpStr(cell_text, "(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])\d{8}", True, True)
        Case "邮箱"
            Picked = PickRegExpStr(cell_text, "[\w!#$%&'*+/=?^_`{|}~-]+(?:\.[\w!#$%&'*+/=?^_`{|}~-]+)*@(?:[\w](?:[\w-]*[\w])?\.)+[\w](?:[\w-]*[\w])?", True, True)
        Case "身份证号"
            Picked = PickRegExpStr(cell_text, "\d{15}|\d{18}", True, True)
        Case "网址"
            Picked = PickRegExpStr(cell_text, "[a-zA-z]+://[\w!#$%&'*+/=?._-]*", True, True)
        Case Else
            Picked = PickRegExpStr(cell_text, pick_str, True, True)
    End Select
    SUPICK = Picked
End Function
Function CleanRegExpStr(TargetRange As Range, myPattern As String, myGlobal As Boolean, myIgnoreCase As Boolean) As String
    Dim mRegExp As Object       '正则表达式对象
    Dim mMatches As Object      '匹配字符串集合对象
    Dim mMatch As Object        '匹配字符串
    Dim mReplace As String      '替换后的字符串
    
    mReplace = TargetRange.Text
    Debug.Print mReplace
    Set mRegExp = CreateObject("Vbscript.Regexp")
    With mRegExp
        .Global = myGlobal                              'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = myIgnoreCase                          'True表示不区分大小写, False表示区分大小写
        .Pattern = myPattern          '"([0-9])?[.]([0-9])+|([0-9])+ 匹配字符模式
        Set mMatches = .Execute(TargetRange.Text)   '执行正则查找，返回所有匹配结果的集合，若未找到，则为空
        For Each mMatch In mMatches
            Debug.Print mMatch
            mReplace = Replace(mReplace, mMatch, "")
            Debug.Print "mReplace:" & mReplace
        Next
        
    End With
    Set mRegExp = Nothing
    Set mMatches = Nothing
    Debug.Print "CLEAN_RESULT:" & mReplace
    CleanRegExpStr = mReplace
End Function
Function PickRegExpStr(TargetRange As Range, myPattern As String, myGlobal As Boolean, myIgnoreCase As Boolean) As String
    Dim mRegExp As Object       '正则表达式对象
    Dim mMatches As Object      '匹配字符串集合对象
    Dim mMatch As Object        '匹配字符串
    Dim mReplace As String      '替换后的字符串
    
    mPick = ""
    Set mRegExp = CreateObject("Vbscript.Regexp")
    With mRegExp
        .Global = myGlobal                              'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = myIgnoreCase                          'True表示不区分大小写, False表示区分大小写
        .Pattern = myPattern          '"([0-9])?[.]([0-9])+|([0-9])+ 匹配字符模式
        Set mMatches = .Execute(TargetRange.Text)   '执行正则查找，返回所有匹配结果的集合，若未找到，则为空
        For Each mMatch In mMatches
            Debug.Print mMatch
            mPick = mPick & mMatch
            Debug.Print "mPick:" & mPick
        Next
        
    End With
    Set mRegExp = Nothing
    Set mMatches = Nothing
    Debug.Print "PICK_RESULT:" & mPick
    PickRegExpStr = mPick
End Function

三、函数使用说明：

1、 FIND_LIST函数
功能:列表匹配单元格并返回匹配成功的结果。
=FIND_LIST(FIND_STR, LIST)
FIND_STR:单元格 如：C6
LIST:待匹配列表 如：F6：F8
例：

2、 SUCLEAN函数
功能：清洗单元格内指定的内容
=SUCLEAN(CELL_TEXT,CLEAN_STR)
CELL_TEXT: 单元格 如：C2
CLEAN_STR: 清洗指令 如：汉字、数字、字母、特殊字符、正则表达式
例：

3、 SUPICK函数
功能：挑拣单元格内指定的内容
=SUPICK(CELL_TEXT,PICK_STR)
CELL_TEXT: 单元格 如：C2
PICK_STR: 清洗指令 如：汉字、数字、字母、手机号、邮箱、身份证号、网址、正则表达式
例：