汉字、数字和字母是三种非常重要的字符串类型,在Excel中,关于这三种字符串类型的字符提取或字符去除是很常用的一种技巧,有些比较规范且规律单一的数据我们可以直接用函数公式提取字符,但是如果我们需要提取或去除不规范数据中的汉字、数字和字母,又该怎么做呢?
提取汉字
首先,按ALT F11弹出VBE窗口,然后新建模块,复制粘贴如下代码:
Function ZW(i As String) As String
Dim a As Object
Set a = CreateObject('VBSCRIPT.REGEXP')
a.Pattern = '[^\u4e00-\u9fa5]'
a.IgnoreCase = True
a.Global = True
ZW = a.Replace(i, '')
Set a = Nothing
End Function
然后,函数公式如下:
=ZW(A2)
提取数字
首先,按ALT F11弹出VBE窗口,然后新建模块,复制粘贴如下代码:
Function SZ(i As String) As String
Dim a As Object
Set a = CreateObject('VBSCRIPT.REGEXP')
a.Pattern = '[^0-9]'
a.IgnoreCase = True
a.Global = True
SZ = a.Replace(i, '')
Set a = Nothing
End Function
然后,函数公式如下:
=SZ(A2)
提取字母
首先,按ALT F11弹出VBE窗口,然后新建模块,复制粘贴如下代码:
Function ZM(i As String) As String
Dim a As Object
Set a = CreateObject('VBSCRIPT.REGEXP')
a.Pattern = '[^A-Z^a-z]'
a.IgnoreCase = True
a.Global = True
ZM = a.Replace(i, '')
Set a = Nothing
End Function
然后,函数公式如下:
=ZM(A2)
去掉汉字
首先,按ALT F11弹出VBE窗口,然后新建模块,复制粘贴如下代码:
Function SZZM(i As String) As String
Dim a As Object
Set a = CreateObject('VBSCRIPT.REGEXP')
a.Pattern = '[^A-Z^a-z^0-9]'
a.IgnoreCase = True
a.Global = True
SZZM = a.Replace(i, '')
Set a = Nothing
End Function
然后,函数公式如下:
=szzm(A2)
去掉数字
首先,按ALT F11弹出VBE窗口,然后新建模块,复制粘贴如下代码:
Function ZWZM(i As String) As String
Dim a As Object
Set a = CreateObject('VBSCRIPT.REGEXP')
a.Pattern = '[^\u4e00-\u9fa5^A-Z^a-z]'
a.IgnoreCase = True
a.Global = True
ZWZM = a.Replace(i, '')
Set a = Nothing
End Function
然后,函数公式如下:
=ZWZM(A2)
去掉字母
首先,按ALT F11弹出VBE窗口,然后新建模块,复制粘贴如下代码:
Function ZWSZ(i As String) As String
Dim a As Object
Set a = CreateObject('VBSCRIPT.REGEXP')
a.Pattern = '[^\u4e00-\u9fa5^0-9]'
a.IgnoreCase = True
a.Global = True
ZWSZ = a.Replace(i, '')
Set a = Nothing
End Function
然后,函数公式如下:
=ZWSZ(A2)
Function ZWSZ(i As String) As String
Dim a As Object
Set a = CreateObject('VBSCRIPT.REGEXP')
a.Pattern = '[^\u4e00-\u9fa5^0-9]'
a.IgnoreCase = True
a.Global = True
ZWSZ = a.Replace(i, '')
Set a = Nothing
End Function
联系客服