SOLVED

Strange GS1 barcode reading error on Excel (but not on Word!)

%3CLINGO-SUB%20id%3D%22lingo-sub-1941493%22%20slang%3D%22en-US%22%3EStrange%20GS1%20barcode%20reading%20error%20on%20Excel%20(but%20not%20on%20Word!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1941493%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CP%3E%3CSPAN%3EHello%2C%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWe%20are%20a%20small%20biomedical%20company%20that%20is%20testing%20new%20product%20labels%20%26amp%3B%20GS1%20barcodes%20to%20comply%20with%20the%20upcoming%20European%20and%20American%20regulations%20for%20Unique%20Device%20Identifiers%20(UDIs).%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERecently%2C%20we%20purchased%20a%20basic%20%3CSPAN%20class%3D%22marku8z95ik1f%22%3EBarcode%3C%2FSPAN%3E%20Scanner%20to%20validate%20our%20labels%20with%20%222D%20Matrix%22%20GS1%20%3CSPAN%20class%3D%22marku8z95ik1f%22%3Ebarcode%3C%2FSPAN%3Es.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHowever%2C%20we%20are%20experiencing%20a%20strange%20phenomenon%20and%20want%20to%20ask%20you%20if%20you've%20come%20across%20something%20like%20this%20(and%20if%20so%2C%20how%20you%20resolved%20it).%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20more%20than%2095%25%20of%20our%202D%20Matrix%20GS1%20%3CSPAN%20class%3D%22marku8z95ik1f%22%3Ebarcode%3C%2FSPAN%3Es%2C%20they%20scan%20perfectly%20on%20both%20MS%20Excel%20and%20MS%20Word-%20all%20data%20scan%20correctly.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%3EHowever%2C%20for%20less%20than%205%25%20of%20our%20individual%20products%2C%20when%20you%20scan%20the%202D%20Gs1%20barcode%2C%20they%20will%20read%20correctly%20on%20Word%2C%20but%20not%20on%20Excel.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20these%20problematic%205%25%2C%26nbsp%3B%20when%20the%20barcode%20is%20scanned%20in%20Excel%2C%20they'll%20%3C%2FSPAN%3E%3CSPAN%3Escan%20INCORRECTLY%20as%20scientific%20notation%20(i.e.%208.3E%5E32).%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWe've%20tried%20re-formatting%20the%20cells%20but%20that%20didn't%20help%20resolve%20the%20issue%20(text%2C%20number%2C%20etc.).%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHave%20you%20have%20come%20across%20this%20type%20of%20%3CSPAN%20class%3D%22markq8yahnxbk%22%3Eerror%3B%20and%3C%2FSPAN%3E%20if%20so%2C%20how%20did%20you%20resolve%20it%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20all%20barcodes%20scan%20100%25%20correctly%20on%20MS%20Word%2C%20but%205%25%20are%20problematic%20in%20MS%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%20for%20your%20help!%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E-Stephen%20%26amp%3B%20Fanny%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1941493%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948770%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20GS1%20barcode%20reading%20error%20on%20Excel%20(but%20not%20on%20Word!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948770%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885983%22%20target%3D%22_blank%22%3E%40supergt62%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20encountered%20the%20exact%20problem.%20I%20am%20wondering%20what%20is%20distinctive%20(unusual%2C%20setting%20them%20apart)%20about%20those%205%25%20of%20the%20barcodes%3F%20Are%20they%20longer%20than%20the%20other%2095%25%2C%20for%20example%3F%20Are%20the%20barcodes%20all%20totally%20numeric%20or%20do%20some%20of%20them%20include%20letters%20or%20other%20figures%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20from%20diagnostic%20experience%2C%20there%20has%20to%20be%20something%20different%20about%20those%20that%20aren't%20scanning%20properly%2C%20especially%20if%20they%20consistently%20scan%20with%20resulting%20scientific%20notation.%20Excel%20does%20have%20a%20limit%20of%2015%20digits%20in%20a%20number%20according%20to%20this%20page%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fexcel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fexcel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3%3C%2FA%3E%3C%2FP%3E%3CP%3ESo%20it's%20likely%20that%20your%205%25%20are%20exceeding%20that.%20Word%20is%20only%20dealing%20with%20them%20as%20text.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1953451%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20GS1%20barcode%20reading%20error%20on%20Excel%20(but%20not%20on%20Word!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1953451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Actually%2C%20the%20number%20of%20digits%20are%20almost%20identical%20across%20all%20barcodes%20(they're%20all%20more%20than%2015%20digits).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-They%20include%20letters%20and%20numbers%20PRIOR%20to%20being%20converted%20to%20the%202D%20matrix%20barcodes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-They%20consist%20of%2014-digit%20GTIN%20number%2C%20Lot%20number%2C%20and%20expiration%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Again%2C%20100%25%20of%20the%20barcodes%20scan%20perfectly%2Fcorrectly%20in%20Word.%26nbsp%3B%20But%20in%20Excel%2C%20about%205%25%20have%20this%20strange%20issue.%26nbsp%3B%20They%20show%20up%20as%20scientific%20notation%20with%20the%20last%20several%20digits%20(lot%20%23%20and%20expiry%20date)%20showing%20up%20as%20all%20zeros%20(...0000000000000)-%20which%20is%20incorrect.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1953664%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20GS1%20barcode%20reading%20error%20on%20Excel%20(but%20not%20on%20Word!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1953664%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885983%22%20target%3D%22_blank%22%3E%40supergt62%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%20%3CEM%3E%22Actually%2C%20the%20number%20of%20digits%20are%20almost%20identical%20across%20all%20barcodes%20(they're%20all%20more%20than%2015%20digits).%22%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20can%20be%20a%20nit-picker%20here%2C%20%22almost%20identical%22%20is%20the%20same%20(linguistically%20speaking)%20as%20%22not%20at%20all%20identical.%22%20When%20it%20comes%20it%20nit-picking%20in%20the%20diagnostic%20process%2C%20the%20cause%20of%20your%20experience%20is%20no%20doubt%20a%20nit%20of%20some%20kind.%20Otherwise%20it%20would%20not%20be%20so%20puzzling.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe're%20looking%20for%20the%20small%20tick%2C%20the%20small%20thing%2C%20whatever%20it%20is%2C%20that%20distinguishes%20the%205%25%20from%20the%2095%25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I%20trust%20you%20realize%20that%20you're%20speaking%20in%20insider-speak%20when%20you%20use%20expressions%20like%20%3CU%3E%3CEM%3E2D%20matrix%3C%2FEM%3E%3C%2FU%3E%20and%2C%20especially%2C%26nbsp%3B%3CU%3E%3CEM%3E14-digit%20%3CSTRONG%3EGTIN%3C%2FSTRONG%3E%20number%3C%2FEM%3E%3C%2FU%3E.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20know%20to%20distinguish%202D%20from%203D%2C%20and%20I%20know%20what%20a%20matrix%20is%2C%20but%20what%20the%20significance%20of%202D%20matrix%20is...no%20knowledge.%20And%20GTIN%3F%20Doesn't%20matter%20to%20me%20how%20many%20digits%2C%20it's%20still%20Greek%20Till%20Interpreted%20Numerically.%20Or%20is%20that%20what%20it%20means%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1953729%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20GS1%20barcode%20reading%20error%20on%20Excel%20(but%20not%20on%20Word!)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1953729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EGTIN%3C%2FSTRONG%3E%20means%20%3CSTRONG%3EGlobal%20Trade%20Item%20Number%3C%2FSTRONG%3E.%20For%20%3CSTRONG%3EGTIN-14%3C%2FSTRONG%3E%2C%20it%20means%20that%20the%20%3CSTRONG%3EGTIN%20consists%20of%2013%20digits%20plus%201%20check%20digit%3C%2FSTRONG%3E%2C%20which%20results%20in%20a%20total%20of%2014%20digits.%20GTIN-14%20has%20the%20maximum%20number%20of%20digits%20among%20all%20GTINs.%20There%20is%20no%20such%20thing%20as%20GTIN-15%20or%20other%20GTINS%20consisting%20of%20more%20than%2014%20digits.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

7 Replies

@supergt62 

 

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-2...

So it's likely that your 5% are exceeding that. Word is only dealing with them as text.

@mathetes 

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.

@supergt62 

 

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?

@mathetes 

GTIN means Global Trade Item Number. For GTIN-14, it means that the GTIN consists of 13 digits plus 1 check digit, which results in a total of 14 digits. GTIN-14 has the maximum number of digits among all GTINs. There is no such thing as GTIN-15 or other GTINS consisting of more than 14 digits.

@Twifoo 

 

Thanks.

 

I actually was pretty sure it was something in that direction, since after all we were talking about product bar codes, but was seeking to make a point...insider language tends not to accomplish what language is intended to do, i.e., communicate.

 

More important, given your knowledge of Excel and its limits, do you have any idea as to why 5% of the codes, when scanned, turn into scientific notation? There has to be something distinctive about those codes, assuming it happens consistently and not randomly.

Best Response confirmed by supergt62 (New Contributor)
Solution
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.
Thank you for your input. We will check and see if that helps.