Re: Use of Like to extract data

Tech-Archive recommends: Fix windows errors by optimizing your registry

On 11 Mar 2007 08:53:45 -0700, rve_52@xxxxxxxxxxx wrote:

Thank you both for your help.

I went through all of the data tyhat I have to use, and this is the
patter

A ###.

i.e. an optional Alpha Character followed by a space. They always
come together, or are absent together. Then up to 3 numeric
characters then a period.

Examples would be

F 1.
F 001.
676.
1.
14.
J 12.


It looks like you optionally want to check for a capital letter and space
preceding your 1-3 digits and a dot.

So the change in sPattern is minimal:

Const sPattern As String = "([A-Z]\s)?\d{1,3}(?=\.)"


or:
=======================================
Option Explicit

Sub test()
Dim s(10) As String
Dim i As Long

s(0) = "abc12.xy"
s(1) = "123.abc"
s(2) = "12ab456.xyz"
s(3) = "12345." 'not sure what you want here
s(4) = "F 1."
s(5) = " F 001."
s(6) = "676."
s(7) = "1."
s(8) = "14."
s(9) = "J 12."
s(10) = "dfdg F 1."

Dim oRegex As RegExp
Dim oMatch As Match
Dim colmatches As MatchCollection
Const sPattern As String = "([A-Z]\s)?\d{1,3}(?=\.)"

Set oRegex = New RegExp
oRegex.Pattern = sPattern
oRegex.Global = True

For i = 0 To UBound(s)
If oRegex.test(s(i)) = True Then
Set colmatches = oRegex.Execute(s(i))
Debug.Print "S" & i, s(i), colmatches(0)
End If
Next i

End Sub
=======================================

Results:

S0 abc12.xy 12
S1 123.abc 123
S2 12ab456.xyz 456
S3 12345. 345
S4 F 1. F 1
S5 F 001. F 001
S6 676. 676
S7 1. 1
S8 14. 14
S9 J 12. J 12
S10 dfdg F 1. F 1



--ron
.

Relevant Pages