Excel Changes the Last Digit to Zero: Keep Long IDs Intact

Is scientific notation hiding digits, or did the value change?

An E in the cell display does not by itself prove that digits were lost. Select the cell and inspect the formula bar, then compare the full value with the original CSV in a text editor. Also check whether Excel stored the ID as text or a number.

The important issue for this sample is that both IDs contain 16 digits. Excel lists numeric precision as 15 digits. An identifier does not tolerate approximation: a one-digit change can select a different product, break a join, or create an apparent duplicate. Increasing the column width is not a remedy for a changed stored value.

If an intact integer is merely displayed in exponential notation, select a Number format with zero decimal places and widen the column to read it. If the source and stored digits differ, that display change cannot repair them. For the two 16-digit sample IDs, use the Text import below.

Import 16-digit and longer CSV IDs as Text

Use the source file that still contains the exact IDs. In Excel desktop, start Data > From Text/CSV, confirm comma separation, and choose the available option to leave detected values as Text before selecting Transform Data. Assign Text to product_id and a whole-number type to stock.

Inspect Applied Steps. If an automatic type step already converted product_id to a number, remove or replace that conversion at the source stage. A later Text conversion cannot reconstruct digits removed earlier. Load the result, then compare the complete two identifiers with the original sample.

Check exact characters, not just length

A damaged identifier may still have 16 characters, especially if a final digit was replaced with zero. Length is a useful screening check, but it cannot establish equality. Compare the complete text with an independent, intact source value.

For a review workbook, prepare C2 as Text before pasting the original ID from an intact plain-text source. Do not copy C2 from the possibly damaged A2. The formulas below compare A2 with that independent reference; matching text does not certify that the source system itself was correct.

=ISTEXT(A2)
Expected: TRUE

=LEN(A2)
Expected: 16

=EXACT(A2,C2)
Expected: TRUE when C2 is the original text 1234567890123456

=SUM(B2:B3)
Expected: 19

Keep the original when digits have changed

If the damaged cell and every saved copy now end in a changed digit, no display format can reveal the original. The useful next step is to find a source-system export, an untouched CSV, or another authoritative copy. Replace the affected identifier from that source and repeat the type check.

Do not guess trailing digits from neighboring rows, add zeros to reach a target length, or use the scientific-notation display as a transcription source. Product numbering schemes are not necessarily sequential, and a plausible-looking code is not evidence of the correct one.

Protect manual entry and later exports

For new manually entered IDs, format the empty destination cells as Text first. An initial apostrophe is also an Excel entry method for a small number of values. Avoid adding apostrophes to a whole CSV as a supposed portable fix: another receiving system may retain them as literal identifier characters.

After exporting again, inspect the actual text file for complete IDs and import it through the receiving workflow. The point to check is every numeric conversion boundary. A workbook that preserves an ID can produce a correct CSV which a later double-click import interprets differently.

Related help: Check whether a CSV export actually lost its leading zeros

Use the long-number setting in Excel 365 or 2024

Microsoft 365 and Excel 2024 offer Automatic Data Conversion settings: File > Options > Data on Windows, or Excel > Preferences > Edit on Mac. Disable the option that retains only the first 15 digits of long numbers.

Apply it before opening or entering new IDs. Power Query still needs its own Text type. This preserves identifiers as text; it does not increase numeric precision or recover changed digits.

Treat a long-digit warning as a type decision

Column Harbor can flag long digit strings as a selected import risk. It does not evaluate XLSX cell types, expand a damaged value into its original ID, or provide arbitrary-precision arithmetic. Use the warning to identify columns that require text preservation and an exact comparison.