0
Follow
0
View

Extracting barcode data using VBA

doberstone 注册会员
2023-01-25 14:58

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.

About the Author

Question Info

Publish Time
2023-01-25 14:58
Update Time
2023-01-25 14:58