1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| Public Function ExtractRegexValues(ByVal Pattern As String, ByVal Target As String) As Collection
Dim Results As Collection
Set Results = New Collection
Dim Rx As Object
Set Rx = CreateObject("VBScript.RegExp")
Rx.Pattern = Pattern
Rx.Global = True
Rx.MultiLine = True
Dim Matchs As Object
Set Matchs = Rx.Execute(Target)
Dim Match As Object
For Each Match In Matchs
Results.Add Match.Value
Next
Set ExtractRegexValues = Results
End Function
Public Sub test()
Dim Results As Collection
Dim data As String, x As Integer
data = ActiveSheet.Range("A1").Value
Set Results = ExtractRegexValues("\d{7}[A-Z]", data)
Dim Item As Variant
x = 1
For Each Item In Results
Debug.Print Item
ActiveSheet.Range("A1").Offset(x, 0).Value = Item
x = x + 1
Next
End Sub |
