I'm creating a schedule in excel that requires some data processing
using three main catagories ProductName, ProductID, and ProductLot.
The ProductLot makes VLOOKUP tricky. Example: Product A has a lot
sequence that goes something like this 10000,10001,10002, 10003, 10004,
10005, A10000, A10001, A10002, A10003, A10004, A10005, B10000...etc,
Product B has a lot sequence 10006,10007,10008, 10009, 10010, A10006,
A10007, A10008, A10009, A10010, B10006...etc. And so on and so on.
What makes this tricky again is that Lots can be split multiple times
creating lot numbers such as A10008A and A10008B where A10008B can
further get slipt like this A10008B1 and A10008B2. So to get ride of
all the junk I don't need and just leave me that 5 digit lot sequence I
have use the below VBA function (not sure on the terminology). WORKS
GREAT! THANK YOU HARLAN! But then when I got to do a VLOOKUP on the
processed info, say A1 is the unproceded info A10008B1 and A2 is the
processed info 10008, in A3 I try to do a VLOOKUP(A2,DATATABLE,3) to
retreieve other important data about the product but I get an error
"#NA". I'm pretty new at excel but was wondering a way to get around
this? Or is there a differnet function that I could use to extract the
5 digit lot sequecne specific to that ProductName and ProductID?

Thanks

bactfarmer

Extract the first set of digits found in another cell.
Function DigitsFirstID(s As String) As String
'Harlan Grove, worksheet.functions, 2003-10-20
'extract first string of digits, based on
'--
http://google.com/groups?threadm=_RK...wsrang er.com
Dim i As Long, j As Long, n As Long
n = Len(s)
i = 1
Do While i <= n And Mid(s, i, 1) Like "[!0-9]"
i = i + 1
Loop
j = i + 1
Do While j <= n And Mid(s, j, 1) Like "[0-9]"
j = j + 1
Loop
DigitsFirstID = Mid(s, i, j - i)
End Function