数据源为0-9的随机数字字符串。需要从这个字符串中找出0-9没出现的数字,如123,即结果为0456789
这里我们用了三种方法去查询出结果,用对比反选,正则表达式和 字典。
作者:Excel小子-Office中国
排除号码效果图
Excel排除号码操作动画
Excel对比排除:
Function wb(rg As Range)
Dim i As Byte
s = "0123456789"
For i = 1 To Len(rg)
s = Replace(s, Mid(rg, i, 1), "")
Next i
wb = s
End Function
正则表达式排除数字:
Function zb(rg As Range)
Dim re
Set re = CreateObject("vbscript.regexp")
s = "0123456789"
re.Global = True
re.Pattern = Replace("[n]", "n", rg.Value)
zb = re.Replace(s, "")
End Function
使用字典排除数字:
Sub jlj()
On Error Resume Next
Dim h
Application.ScreenUpdating = False
g = Cells(Rows.Count, 1).End(xlUp).Row
arr = Range("a1:a" & g)
For o = 1 To UBound(arr)
Set zz = CreateObject("VBScript.RegExp")
Set zd1 = CreateObject("scripting.dictionary")
Set zd = CreateObject("scripting.dictionary")
zz.Global = True
zz.Pattern = "\d"
Set m = zz.Execute(arr(o, 1))
For Each n In m
zd(n * 1) = ""
Next
For t = 0 To 9
zd1(t) = ""
Next
v = zd1.keys
For Each b In zd.keys
For c = 0 To 9
If b * 1 = v(c) Then
zd1.Remove (v(c))
GoTo 100
End If
Next
100:
Next
For Each r In zd1.keys
h = h & r
Next
i = i + 1
Cells(i, 5) = h
Set zd = Nothing: Set zd1 = Nothing: h = Nothing: v = Nothing: m = Nothing
Next
End Sub
如喜欢此技巧,手机右上角点开,分享到QQ空间,方便自己以后看