用法:=tq(选择单元格, 输入提取类型),图例如下:
1、去掉汉字,公式:=tq(A3,"-hz")
2、提取汉字,公式:=tq(A3,"+hz")
3、提取数字,公式:=tq(A3,"+sz")
其他用法类似,依此类推即可。第二个参数提取类型解释:
+hz:取汉字
+sz:取数字
+zm:取字母
-hz:取非汉字
-sz:取非数字
-zm:取非字母
自定义函数代码:
Function TQ(rng As String, types As String) As String
Dim regex As Object
Set regex = CreateObject("vbscript.regexp")
With regex
.Global = True
Select Case types
Case Is = "-hz"
'提取去汉字
.Pattern = "[一-﨩]"
Case Is = "-zm"
'提取去字母
.Pattern = "[a-zA-Z]"
Case Is = "-sz"
'提取去数字
.Pattern = "[0-9\.]"
Case Is = "+hz"
'取汉字
.Pattern = "[^一-﨩]"
Case Is = "+zm"
'取字母
.Pattern = "[^a-zA-Z]"
Case Is = "+sz"
'取数字
.Pattern = "[^0-9\.]"
End Select
TQ = .Replace(rng, "")
End With
Set regex = Nothing
End Function
领取专属 10元无门槛券
私享最新 技术干货