barcode text extraction

Watchdawg72

New member
Joined
Jun 19, 2020
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2016
I'm beginning to think this can't be done, but hopefully someone here has a solution...
I scan a tag and and this number is what populates excel: 01050002234203071724501101919B
from that number, the qualifiers are bold.
I need to extract the numbers between the "01" and "17" into one cell, between "17" and "10" in another, and those following the "10" in another.
Unfortunately there are two snags. The first set of numbers I need are either 13 or 14 digits long (no real rhyme or reason). Additionally, the "17" an "10" occasionally change places.
Is this even possible?:frusty:
 
It's certainly possible using the MID function to extract substrings. Don't the 17 and 10 fields represent expiration date and lot number? Are these a fixed number of characters? You have 5 characters following the 17 but in practice I believe there should be 6 (YYMMDD).

Given the first 2 characters are 01 and the right 14 characters contain the 17 and 10 fields., the 01 code is

=MID(barcode,3,len(barcode)-16)

The 17 and 10 field can be extracted from the rightmost characters

=RIGHT(barcode,14)

see the attached for the formulas. I made the length of the 17 and 10 fields variable if you need to change them.
 

Attachments

  • Barcodes.xlsx
    9.3 KB · Views: 6
Thanks for the input Norm. Actually, you are correct. The lot number follows the 10 and the expiry date follows the 17. That said, some vendors supply MM-YYYY, some are MM-DD-YY, the formats vary. Additionally, the 17 and the 10 are sometimes swapped in the barcode, 01 is always the first portion, however, those numbers vary between 13 digits and 14 digits, hence my delima
 
Back
Top