Pulling specific data from a scanned barcode, eliminating qualifiers

[Watchdawg]

I've seen a few things out there, but I haven't found anything that addresses multiple qualifiers, so here we go:
On Sheet 2, column A contains the part number, Column B contains the EIN number. Sheet 1 is where you would use a barcode scanner to scan a label. The format of the scanned barcode is this: (01)123456789(10)12345(17)123456. Qualifier (01) is always the same, The other two sometimes are in different positions. The length of the numbers between the qualifiers is not consistantly the same unfortunately. I need a way for VBA to see that scan, extract the number between the first two qualifiers and place it in a cell, then do the same for the numbers between the next two qualifiers, utilizing a total of 3 cells. From there I have to cross-reference a sheet to extract some other data (which I can do).
Any help would be greatly appreciated.

 

[jasonb75]

Is this something that you can work with?

Sub split_barcode()
Dim bCode As String, bArray As Variant, a As Long
bCode = "(01)123456789(10)12345(17)123456"
bCode = Replace(Replace(bCode, "(", ","), ")", ",")
bArray = Split(bCode, ",")
For a = 2 To UBound(bArray) Step 2
    Debug.Print bArray(a)
Next
End Sub

 

[Watchdawg]

Is this something that you can work with?

Sub split_barcode()
Dim bCode As String, bArray As Variant, a As Long
bCode = "(01)123456789(10)12345(17)123456"
bCode = Replace(Replace(bCode, "(", ","), ")", ",")
bArray = Split(bCode, ",")
For a = 2 To UBound(bArray) Step 2
    Debug.Print bArray(a)
Next
End Sub

Thanks for that, jasonb, but it made me dig a little deeper. The scan actually removes the "( )" so it's just numbers that appear, so the replace method wouldn't work (however, that code will help me in the future I'm sure!).

 

[jasonb75]

What do you actually need as a result of this part? I read it that you wanted to extract the 3 longer numbers (in bold below) from the original barcode.

(01)123456789(10)12345(17)123456

Another option, is this closer to what you need?

Sub split_barcode()
Dim bCode As String, bArray As Variant, a As Long
bCode = "(01)123456789(10)12345(17)123456"
bCode = Replace(Replace(bCode, "(", ",("), ")", "),")
bArray = Split(bCode, ",")
For a = 1 To UBound(bArray)
    Debug.Print bArray(a)
Next
End Sub

 

[Watchdawg]

What do you actually need as a result of this part? I read it that you wanted to extract the 3 longer numbers (in bold below) from the original barcode.

(01)123456789(10)12345(17)123456

Another option, is this closer to what you need?

Sub split_barcode()
Dim bCode As String, bArray As Variant, a As Long
bCode = "(01)123456789(10)12345(17)123456"
bCode = Replace(Replace(bCode, "(", ",("), ")", "),")
bArray = Split(bCode, ",")
For a = 1 To UBound(bArray)
    Debug.Print bArray(a)
Next
End Sub

Unfortunately, I discovered that when the barcode scans, it automatically removes parintheses. So what actually populates is 01123456789101234517123456. The bold numbers would what I would need extracting into 3 different cells, unfortunately those numbers are not always the same amount of characters.

 

[jasonb75]

Done a quick search, it looks like barcodes don't support parentheses so that explains why they're being stripped out, without them or another identifier it is not going to be possible to split the string down as there a no 'knowns' to work with.

 

[Watchdawg]

UPDATE - OK, here's where I am right now. When the barcode is scanned, it returns the following number "01050002234819261723010110980160". In this case, I needed everything between the initial "01" and the "17". I managed this with the following formula - "=MID(LEFT(A1,FIND("17",A1)-1),FIND("01",A1)+2,LEN(A1))". What I need to be able to do is tell the formula to look for a "17" OR a "10". Is that possible in the body of this formula? It seems complicated enough as it is...

 

[jasonb75]

It's possible, but the hard part would be finding the correct "10" or "17" in the string, just in the one example that you have provided there are 3 instances of "10" in others there could potentially be even more.

"01050002234819261723010110980160"

You have already eliminated character count as a possible way to find the position in post #1. Unless you are able to change to a different barcode type that supports ( and ) I think that you will have an impossible task.

I believe that QR code, Data Matrix, Aztec, Code 128 and PDF 417 support such characters, what I have not looked into is compatibility of such barcode types with excel, I would suspect that code 128 would be the only one that might possibly be compatible.

 

[Watchdawg]

It's possible, but the hard part would be finding the correct "10" or "17" in the string, just in the one example that you have provided there are 3 instances of "10" in others there could potentially be even more.

"01050002234819261723010110980160"

You have already eliminated character count as a possible way to find the position in post #1. Unless you are able to change to a different barcode type that supports ( and ) I think that you will have an impossible task.

I believe that QR code, Data Matrix, Aztec, Code 128 and PDF 417 support such characters, what I have not looked into is compatibility of such barcode types with excel, I would suspect that code 128 would be the only one that might possibly be compatible.

Because it finds the "01" before the 10 in the beginning, it gets past that pretty easily (all of the numbers start with 01. What was killing me was the difference between a 17 identifier or a 10 identifier in the same position. I discovered that the function returns an error if you tell it to find the 17 and it doesn't, so I did this and it seems to be a decent work around...
=MID(LEFT($A14,IFERROR(FIND("17",$A14),FIND("10",$A14)-1)),FIND("01",$A14)+2,LEN($A14))
Would be nice if it was a bit cleaner (cause god knows that's hard to read), but we don't fight with the warriors we want, we fight with the warriors we have...