Extracting barcode data using VBA-CodePudding

I need help. I developed an Excel sheet that as I scan an employee barcode it will extract the base-32 code information so I can get the employee ID number, first name and last name using different formulas. Excel Sheet

The only problem is the formulas to extract this data is different based on how the code starts out as seen in the Excel Sheet. I can use the IFS formula in Excel on O365 but all of our agencies use the standard desktop version of Excel.

My question; is there a way to code out in VBA that when an ID is scanned, regardless of what the scanned code starts with, that it will perform the needed formula to extract the three items I need which is ID, first name and last name? Below are the formulas I use:

Scan starting with "M"

Base-32 =MID(A2,2,7)

First Name =PROPER(MID(A2,17,20))

Last Name =PROPER(MID(A2,38,20))

Scan Starting with "N"

Base-32 =MID(A3,9,7)

First Name =PROPER(MID(A3,16,20))

Last Name =PROPER(MID(A3,36,26))

Scan Starting with "C"

Base -32 =MID(A4,8,7)

First Name =PROPER(MID(A4,15,20))

Last Name =PROPER(MID(A4,35,20))

ID NUMBER

The ID number for each of them is calculated the same (based on the cell the scan goes in to) using:

=IF(C2="","0",SUMPRODUCT(POWER(32,LEN(C2)-ROW(INDIRECT("1:"&LEN(C2)))),(CODE(UPPER(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)))-48*(CODE(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1))<58)-55*(CODE(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1))>64))))

Thank you in advance to anyone that can help.

CodePudding user response：

Not sure if this is exactly in line with your requirements, but the following UDF could be used to retrieve your data:

Function GetData(inp As String, grp As Long) As String

With CreateObject("VBScript.RegExp")
    .Pattern = "^(?:M|N.{7}|C.{6})(.{7})\S*([A-Z][a-z] )\s*(\S )"
    If .Test(inp) Then
        GetData = .Execute(inp)(0).Submatches(grp - 1)
    Else
        GetData = "No Data Found"
    End If    
End With

End Function

Here is an online demo how the pattern works. It would match:

^ - Start line anchor.
(?:M|N.{7}|C.{6}) - A non-capture group to either capture a literal 'M', or a literal 'N' followed by 7 characters other than newline, or a literal 'C' followed by 6 of those characters.
(.{7} - Then a 1st capture group of 7 characters to mimic the MID() functionality, capturing the Base-32 code.
\S* - 0 (Greedy) non-whitespace characters, upto:
([A-Z][a-z] ) - A 2nd capture group to capture the lastname through a single uppercase alphachar and 1 (Greedy) lowercase ones.
\s* - 0 (Greedy) whitespace characters upto:
(\S ) - A 3rd capture group to catch the first name through 1 (Greedy) non-whitespace characters.

You'd call this function in your sheet through =GetData(A1,1) to get the 'Base-32' code and use integer 2 to get the last name and a 3 to get the first name. I hope that helped a bit.