trouble parsing variable length string with numbers and characters 2

[Xlyop]

I have a field in a db that has serial numbers in it. the serial numbers are of variable length with combinations of numbers, characters, and some random symbols
examples:

123D0420003
1F3212222
*9993345F412*
FG345K0924567
the field length is as short as 7 characters and as long as 22.
it is a semi- smart number where four characters denote Vendor Week produced and Year produced but the four characters are not in the same location everytime.
Example 1) 123D0420003 - 123 D042 0003 - D = VendorID 04 = 4th week 2 = 2002
Example 2) 1F3212222 - 1 F321 2222 - F = VendorID 32 = 32nd week 1 = 2001
I am trying to use a pattern matching function and then strip out the pertinent values
I have identified a consistent pattern of Number Letter Number Number Number but have not been successful in getting the function to perform.
Any help would be greatly appreciated before I go off the deep end

Thanks

 

[Shake412]

Try this for size. It fits the 4 examples, but I make some assumptions so I don't know if it will work for everything. Let me know how you get on.

Sub Serial()
Dim SerialNo As String
Dim Length As Integer
Dim Position As Integer
Dim VendorID As String
Dim WeekNo As Integer
Dim Year As Long

SerialNo = UCase("*9993345F412*&quot
Length = Len(SerialNo)
For Position = Length - 3 To 1 Step -1
If Asc(Mid(SerialNo, Position, 1)) >= 65 And Asc(Mid(SerialNo, Position, 1)) <= 90 Then
VendorID = Mid(SerialNo, Position, 1)
WeekNo = CInt(Mid(SerialNo, Position + 1, 2))
Year = CLng(Mid(SerialNo, Position + 3, 1)) + 2000
Exit For
End If
Next

MsgBox &quot;VendorID = &quot; & VendorID & vbCrLf & &quot;WeekNo = &quot; & WeekNo & vbCrLf & &quot;Year = &quot; & Year

End Sub

 

[Xlyop]

Thanks Shake412,

I used your code with just minor modifications and it worked perfectly. all I added was an error trap .. cause, of course, some of the data still doesn't match the pattern .. luckly it was very old data and I could ignore it.

Thanks again,

Xlyop