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

Is this scientific notation or lost digits?

Open the untouched CSV in a plain text editor and find one affected ID. In Excel, select its cell and inspect the formula bar as well as the displayed cell. Scientific notation describes the display: the letter E alone does not prove the stored digits changed. Compare the complete value with the source, not just its visible prefix.

For an intact numeric value, Number format with zero decimal places and a wider column can show the integer normally. If the underlying value already has different final digits, widening or reformatting only reveals that changed value. Do not save over the only original CSV while investigating.

Use the sample files to check a real import

Download long-id-input.csv and keep that file unchanged. It has a header plus four synthetic records, so a worksheet import occupies A1:C5: case_id in A, product_id in B and stock in C. The first ID is a 15-digit control. The next two have 16 digits and differ only in their final character; a successful import must keep them distinct.

The import-expected download lists the acceptance checks, not an alternative set of customer records. CSV itself has no Excel column-type metadata: downloading or double-clicking an expected file does not enforce Text. Follow the explicit import procedure before using its values as a reference.

The separate known-damage download deliberately contains already changed numbers alongside untouched reference strings. It demonstrates why a later TEXT formula cannot repair lost information. Its values are a controlled counterexample, not a claim that every Excel version, setting or import route produces exactly the same conversion.

Import the CSV identifier column as Text before conversion

In desktop Excel versions with the Text/CSV Power Query connector, start from the Data tab rather than opening the CSV by double-clicking it. Preview labels vary by version. The important boundary is the first conversion of product_id: changing a later, already damaged number to Text keeps the damaged spelling.

For an Excel installation using the legacy Text Import Wizard, choose a delimited file, comma delimiter and Text for the product_id column before completing the import. If a CSV bypasses that wizard, use a .txt copy of the same source to select its columns explicitly. The original file should remain unchanged.

1. Choose Data → From Text/CSV and select long-id-input.csv. Set the delimiter to comma and check that the preview has three columns.
2. If the preview offers Data Type Detection, choose the option that does not detect data types, leaving columns as Text. Then choose Transform Data.
3. Inspect Applied Steps. Remove an automatically inferred numeric Changed Type step for the IDs, or replace that conversion so product_id becomes Text directly from the original field. Check that an earlier step has not already changed its digits.
4. Set case_id and product_id to Text. Set stock to Whole Number. The four source IDs must still read 123456789012345, 1234567890123456, 1234567890123457 and 9876543210987654.
5. Close & Load to a worksheet, positioning the three-column table at A1 for the checks below. If it loads elsewhere, adjust all referenced ranges consistently.

Verify characters, types and the quantity total

After the safe import, prepare D1 as original_id and format the empty cells D2:D5 as Text. Copy the four original product_id strings into D2:D5 directly from the untouched CSV opened in a text editor. Do not populate D from B: comparing a damaged cell with a copy of itself would pass while proving nothing about the source.

Enter the formulas below in row 2 and fill E2:H2 down through row 5. They use English function names and comma separators; some Excel locales require different separators or translated function names. All four type checks and all four exact comparisons should be TRUE. Length is a supporting check, not proof of correctness: an incorrect 16-digit value can still contain 16 characters.

Passing a stock total is not enough either. A file can have the correct quantities but damaged product IDs. Check the identifier column and the numeric column separately so arithmetic remains possible without sacrificing the matching key.

Reproduce a harmless scientific-notation display

On a separate sheet named Display, enter the 15-digit number 123456789012345 in A2. Prepare B2 as Text before entering an independent copy of those digits. In C2 enter =TEXT(A2,"0"); in D2 enter =EXACT(C2,B2). D2 should be TRUE.

Apply the custom number format 0.00E+00 to A2. With sufficient column width it displays 1.23E+14. Now change A2 to Number with zero decimals and widen the column: it shows 123456789012345. The displayed form changes while C2 and the TRUE comparison stay the same.

This control explains why E notation is not a diagnosis by itself. It is deliberately limited to an intact 15-digit integer. The corresponding TEXT formula applied to a changed 16-digit numeric ID cannot look back at the original CSV. For business identifiers, importing all ID rows as Text is a simpler consistent rule than deciding row by row which happen to fit numeric precision.

Why TEXT and a 16-digit format cannot repair a changed ID

For the counterexample, import long-id-known-damage.csv on a separate sheet named Damage, with case_id and original_id as Text and already_changed_number as a number. The table occupies A1:C5. In D2 enter =TEXT(C2,"0"), in E2 enter =EXACT(B2,D2), and in F2 enter =LEN(D2); fill D2:F2 down through row 5.

The intact control passes. The other three comparisons return FALSE even though their TEXT results are 16 characters long. Rows 3 and 4 now produce the same string, 1234567890123450, despite having different original IDs. Once those different originals lead to the same available value, that value alone cannot tell you which final digit belonged to which record.

A format such as 0000000000000000 can supply display padding; it does not recover an unknown suffix. Changing the cell format to Text after conversion, concatenating an empty string, or using TEXT creates no access to the missing original characters. Re-entering a verified original into a Text cell is different: it brings the lost information back from another source.

Recover from an untouched source and check the next export

If a stored identifier fails the independent comparison, obtain the original export, a source-system record or a backup from before conversion. Reimport that source with the ID column as Text, then repeat the exact checks. If the only available file already contains changed digits, ask the source owner for a fresh authoritative export; formatting cannot establish the missing characters.

Do not deduplicate or merge using the damaged ID and assume the result will identify the original rows. The two-record counterexample shows why that key can become ambiguous. Rebuild the affected import from the intact source, or use a separately verified key to connect records.

When exporting the repaired data as CSV, inspect the saved file in a plain text editor before opening it in another spreadsheet. Confirm the literal ID strings and require the receiving system to treat them as Text. Quoting a CSV field controls CSV syntax; it is not an Excel type declaration. Repeat the comparison at the next import boundary.

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

Prevent conversion during manual entry and future imports

For a few manually entered IDs, format empty destination cells as Text before typing or pasting. A leading apostrophe can mark a single manual entry as text in Excel, but do not add apostrophes indiscriminately to source CSV values: another importer can treat them as literal ID characters.

Microsoft documents Automatic Data Conversions controls for Excel for Microsoft 365 and Excel 2024 on Windows and Mac. Where available, turn off the conversion that truncates numerical text to 15 digits before entering new long IDs. This is prevention for supported entry paths, not an increase in numeric precision and not a repair of existing cells. Power Query still needs its own appropriate column types.

The Column Harbor CSV checker can flag long digit strings as a prompt to review the intended type. It cannot inspect an XLSX cell type or reconstruct digits that are absent from the supplied file. Use its warning to decide which columns need exact Text handling, then verify the completed spreadsheet import with the independent original.

On September 13, 2026, we tested the synthetic file in Excel 16.0, build 5569.0, using its QueryTable text-import engine with explicit column types. Text import preserved all four IDs and stock totaled 26. General import changed the three 16-digit IDs, including the two-ID collision shown here. These observations describe that route and build; the Power Query dialog and every Excel version were not tested.