如果在 A 列中,我的值如下一个
编号:A0394
948B0129
Zkjs333
0a0401a
09ab28
我想返回匹配项,其中有 2 个字母,后跟 2 个数字字符,使用正则表达式和 VBA(不使用自定义函数(乙
JS33
AB28
代码会是什么样子?
你几乎拥有了它。由于您只搜索模式的一个匹配项,例如第一个,您可以将其作为matches(0)
,但首先使用 matches.count
检查是否存在匹配项。
Sub Test()
Dim cel As Range, matches As Object
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[a-zA-Z]{2}[0-9]{2}"
For Each cel In Range("A1:A10")
Set matches = .Execute(cel.Value2)
If matches.Count > 0 Then cel.Offset(0, 1).Value = matches(0)
Next
End With
End Sub
您的正则表达式是正确的,应该这样的东西才能在 VBA 中正确定义它
Private Sub simpleRegex()
Dim strPattern As String: strPattern = "[a-z]{2}[0-9]{2}"
Dim regEx As New RegExp
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
跟
regEx.Test(strInput)
您可以检查字符串是否匹配。
你可以在这里找到一个非常深刻的答案。
Sub test()
Dim matches, regex As Object, c As Range
Dim i As Long
Set regex = CreateObject("VBScript.RegExp")
With regex
.Pattern = "[a-z]{2}[0-9]{2}"
.Global = True
End With
For Each c In Range("A1:A4")
Set matches = regex.Execute(c.Value)
'if only one match expected...
If matches.Count > 0 Then
c.Offset(0, 1) = matches(0)
End If
'if can be multiple matches...
'For i = 1 To matches.Count
' c.Offset(0, i) = matches(i - 1)
'Next i
Next
End Sub