Scientific notation
Scientific notation is a short way to write a long number, like 5.01235E+12, and a barcode that leaves a spreadsheet in that form has lost digits.
- Exponential notation
What it means
Scientific notation shortens a long number to a few digits and a power of 10. In Microsoft’s words, the E “multiplies the preceding number by 10 to the nth power”, so 5.01235E+12 means 5.01235 times 10 to the 12th power. Excel’s General format uses it for numbers of 12 or more digits, so a 13 digit EAN can appear as 5.01235E+12.1
Whether the barcode survives depends on where it is. Inside the original workbook, all 13 digits may still be there. A number format “doesn’t affect the actual cell value”,1 and Excel holds up to 15 digits of a number.2 Once the value travels as text, in a CSV or pasted into another system, 5.01235E+12 keeps six digits. The other seven are gone.
Supplier codes get caught too. A code like 123E5 is read as a number and shown as 1.23E+07.3 A code of 16 digits or more has every digit after the 15th “rounded down to zero”.2
Think of it like this
It is like a phone book that rounds every number to the nearest thousand. Each entry is close to the real one, and not a single call goes through.
For example
A supplier’s EAN 5012345678900 shows in Excel as 5.01235E+12. In that workbook, a format such as Number may bring all 13 digits back.1 In a CSV that already reads 5.01235E+12, the number behind it is 5012350000000: 13 digits, a check digit that passes, and the wrong barcode.
Why it matters
Once the digits have gone, no setting or formula brings them back. The only repair is the original: ask the supplier for their export again, or look each product up. Prevention is easier. Set code columns to Text before any data goes in, because formatting them afterwards “will not change numbers that have already been entered”.2
Not to be confused with
A lost leading zero is a different fault. A dropped zero can be put back and checked with the check digit, but digits lost to scientific notation cannot be worked out from what is left.
Sources
- Microsoft Support. Display numbers in scientific (exponential) notation.
- Microsoft Support. Keeping leading zeros and large numbers.
- Microsoft Support. Set automatic data conversions.