Extracting barcode data using VBA

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:

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

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

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

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

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

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

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

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

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

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.

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', a literal 'N' followed by 7 characters other than newline, 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.

Extracting barcode data using VBA

0 Comment

0 Comment

About the Author

Question Info

Related Question

<p data-section="0"><span data-section="0" data-sentence="0">Pytorch自定义丢失(对比学习)无法正常工作</span></p>

关于#vue.js#的问题：Transition的钩子函数el类型报错：类型“Element”上不存在属性“dataset”(语言-typescript)

How can we create a field to check the duration of a stage in an opportunity in salesforce using fields and flows?

如何使用脚本或作业管理器在特定时间重新启动Databricks集群

InceptionResnetV2 error: operatornotallowwedingrapherror:using a ' tf. errorTensor '作为Python ' bool '是不允许的:AutoGraph没有转换这个函数

如何從metadata.csv載入全部的CBIS-DDSM資料?

Powershell how to extract data from JSON file based on one parameter

R中的线性回归:警告:'newdata'有1行,但发现变量有392行

如何创建物化视图在delta活表在Databricks使用pyspark?

<p data-section="0"><span data-section="0" data-sentence="0">在javascript中使用数组集</span></p>