Forum Discussion
Strange GS1 barcode reading error on Excel (but not on Word!)
Hello,
We are a small biomedical company that is testing new product labels & GS1 barcodes to comply with the upcoming European and American regulations for Unique Device Identifiers (UDIs).
Recently, we purchased a basic Barcode Scanner to validate our labels with "2D Matrix" GS1 barcodes.
However, we are experiencing a strange phenomenon and want to ask you if you've come across something like this (and if so, how you resolved it).
For more than 95% of our 2D Matrix GS1 barcodes, they scan perfectly on both MS Excel and MS Word- all data scan correctly. However, for less than 5% of our individual products, when you scan the 2D Gs1 barcode, they will read correctly on Word, but not on Excel.
For these problematic 5%, when the barcode is scanned in Excel, they'll scan INCORRECTLY as scientific notation (i.e. 8.3E^32).
We've tried re-formatting the cells but that didn't help resolve the issue (text, number, etc.).
Have you have come across this type of error; and if so, how did you resolve it?
Again, all barcodes scan 100% correctly on MS Word, but 5% are problematic in MS Excel.
Thank you for your help!
-Stephen & Fanny
- I guess the codes are converted to scientific notation because they are more than the allowed maximum 15 significant digits in Excel. I hope that the GTIN, Lot Number, and Expiry Date could be exported to Excel in separate columns.
8 Replies
I haven't encountered the exact problem. I am wondering what is distinctive (unusual, setting them apart) about those 5% of the barcodes? Are they longer than the other 95%, for example? Are the barcodes all totally numeric or do some of them include letters or other figures?
Just from diagnostic experience, there has to be something different about those that aren't scanning properly, especially if they consistently scan with resulting scientific notation. Excel does have a limit of 15 digits in a number according to this page: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
So it's likely that your 5% are exceeding that. Word is only dealing with them as text.
Thank you for your reply.
-Actually, the number of digits are almost identical across all barcodes (they're all more than 15 digits).
-They include letters and numbers PRIOR to being converted to the 2D matrix barcodes.
-They consist of 14-digit GTIN number, Lot number, and expiration date.
-Again, 100% of the barcodes scan perfectly/correctly in Word. But in Excel, about 5% have this strange issue. They show up as scientific notation with the last several digits (lot # and expiry date) showing up as all zeros (...0000000000000)- which is incorrect.
You wrote "Actually, the number of digits are almost identical across all barcodes (they're all more than 15 digits)."
If I can be a nit-picker here, "almost identical" is the same (linguistically speaking) as "not at all identical." When it comes it nit-picking in the diagnostic process, the cause of your experience is no doubt a nit of some kind. Otherwise it would not be so puzzling.
We're looking for the small tick, the small thing, whatever it is, that distinguishes the 5% from the 95%.
And I trust you realize that you're speaking in insider-speak when you use expressions like 2D matrix and, especially, 14-digit GTIN number.
I do know to distinguish 2D from 3D, and I know what a matrix is, but what the significance of 2D matrix is...no knowledge. And GTIN? Doesn't matter to me how many digits, it's still Greek Till Interpreted Numerically. Or is that what it means?
