EXCELvba 封装函数之判断是否含有中文字符和取得中文字符

bluesky_0

函数1 判断字符串(单元格)是否含有中文字符

1. <blockquote>Function HasChineseCharacters(inputStr As String) As Boolean

复制代码

函数2:
     提取字符串(单元格)的中文---正则表达式方法

1. Function ExtractChineseCharactersRegex(inputStr As String) As String
   
2.     Dim regex As Object
   
3.     Dim matches As Object
   
4.     Dim match As Object
   
5.     Dim chineseChars As String
   
6.    
   
7.     chineseChars = ""
   
8.    
   
9.     Set regex = CreateObject("VBScript.RegExp")
   
10.     regex.Global = True
    
11.     regex.IgnoreCase = True
    
12.     regex.Pattern = "[\u4e00-\u9fa5]+" ' 匹配中文字符的正则表达式
    
13.    
    
14.     Set matches = regex.Execute(inputStr)
    
15.    
    
16.     For Each match In matches
    
17.         chineseChars = chineseChars & match.Value
    
18.     Next match
    
19.    
    
20.     ExtractChineseCharactersRegex = chineseChars
    
21. End Function

复制代码

     提取字符串(单元格)的中文-MID方法
这个方法会丢失中文字符,仅供参考;问题出在MID 对中文双字符的处理上

1. Function ExtractChineseCharacters(inputStr As String) As String
   
2.     Dim i As Integer
   
3.     Dim chineseChars As String
   
4.     Dim charCode As Long
   
5.    
   
6.     chineseChars = ""
   
7.    
   
8.     For i = 1 To Len(inputStr)
   
9.         charCode = AscW(Mid(inputStr, i, 1))
   
10.         If (charCode >= 19968 And charCode <= 40869) Then '中文字符范围
    
11.             chineseChars = chineseChars & Mid(inputStr, i, 1)
    
12.         End If
    
13.     Next i
    
14.    
    
15.     ExtractChineseCharacters = chineseChars
    
16. End Function

复制代码

bluesky_0

第一个函数的内容,我死活搞不定,修改几次,每次都是线上那个玩意,不知道咋会回事,
我开始以为电脑问题,我还重启电脑,然后修改帖子,也不行
下面是源代码:

1. Function HasChineseCharacters(inputStr As String) As Boolean
   
2.     Dim i As Integer
   
3.     Dim charCode As Long
   
4.    
   
5.     For i = 1 To Len(inputStr)
   
6.         charCode = AscW(Mid(inputStr, i, 1))
   
7.         If (charCode >= 19968 And charCode <= 40869) Then '中文字符范围
   
8.             HasChineseCharacters = True
   
9.             Exit Function
   
10.         End If
    
11.     Next i
    
12.    
    
13.     HasChineseCharacters = False
    
14. End Function

复制代码

taller

taller

bluesky_0

taller 发表于 2024-4-25 06:00

感谢taller版主,简化代码,效率高,
版主截图源代码如下:
Function ExtractChineseCharacters(inputStr As String) As String
    Dim regex As Object

    Set regex = CreateObject("VBScript.RegExp")
    regex.Global = True
    regex.IgnoreCase = True
    regex.Pattern = "[^\u4e00-\u9fa5]+" ' 匹配非中文字符的正则表达式   
        
    ExtractChineseCharacters = regex.Replace(inputStr,"")
End Function