Keep long ID numbers intact in Excel

Preserve every character in long reference numbers. Learn why widening a column cannot recover digits lost during numeric conversion.

Import long reference numbers as Text, even if every character is a digit. Excel numbers have a limit of 15 significant digits. An identifier with more digits should not be stored as a number if you need to preserve it exactly. Microsoft's explanation of long numbers

A wider column or a different display format cannot restore digits that were lost in a numeric conversion. Start again from the intact source.

Two IDs that must stay different

Download identifier-practice.csv. These are fictional reference numbers, not bank, identity or customer records.

The first two values differ only near the end. Looking at the first few digits is not enough to verify them. A shortened display can hide the difference, while numeric conversion can actually destroy it.

Start from the original CSV

Close any automatically opened copy without overwriting the download. Use a text-import preview that lets you select the reference column before loading; the recorded Mac File → Open route is explained in the encoding guide. If no column-type choice appears, do not accept the automatic import and then format its rounded numbers as text. Keep the original file available for the two exact-string comparisons below.

Set the type before the import

We checked this sample in Excel for Mac 16.112.2 on macOS 26.6.2 using a Chinese interface. The English labels below are translations. The full values in both identifier columns, including blanks, matched the original strings after import.

For the Text Import Wizard:

1. Open the CSV through a text import route. For this sample, use Delimited, row 1, and Unicode (UTF-8).
2. Choose Comma with a double quote text qualifier. Do not combine consecutive delimiters.
3. Select reference_id in the preview and choose Text. Set product_code to Text too.
4. Finish the import and compare the complete values with the table above.

Settings that protect the reference

This English checklist summarizes the tested import settings, rather than reproducing the Excel interface. For Power Query, set identifiers to Text before an automatic numeric conversion. If a Changed Type step has already converted them, remove or replace that conversion and reload from the original source. Converting the resulting rounded number into text afterward preserves the wrong value. This guide has not tested the Power Query route.

Do not assume that surrounding a CSV field with quotes forces Excel to preserve it as text. Choose the column type in the import settings.

Check all the characters

With the sample headers on row 1, C2 should be 123456789012345671 and C3 should be 123456789012345679. Select each cell and inspect the full value in the formula bar.

Optional checks in unused cells are:

=EXACT(C2,"123456789012345671")
=EXACT(C3,"123456789012345679")
=ISTEXT(C2)
=LEN(C2)

Expected results: TRUE, TRUE, TRUE and 18. The quotes around the expected ID in these formulas matter: they make the comparison target text rather than another numeric conversion. Function names and argument separators can vary by Excel language and region.

Then verify that C4 still starts with 000, the alphanumeric ID is unchanged, and the final reference is blank. These edge cases help catch an overbroad “convert everything to numbers” step.

Recognize a wrong result

For this fixture, 123456789012345671 and 123456789012345679 must remain different strings. A result such as 123456789012345000 fails even though its length is still 18. Do not use LEN alone as the acceptance check.

Place the four optional formulas in separate empty cells outside A:D. If EXACT is FALSE, compare the original downloaded file with the formula bar before editing anything. Reimport from the source if Excel already changed the digits; manually filling in a plausible suffix is not a repair.

Scientific notation does not prove damage

Seeing 1.23457E+14 is a display observation, not by itself proof that the stored value changed. Inspect the full value and compare it with the source. A short number may simply need a wider column or an appropriate display format.

An 18-digit identifier that was converted into an Excel number has a different problem: the numeric representation cannot preserve all significant digits. Changing its format later cannot reconstruct the original suffix.

Recover from an intact source

Close the altered copy without overwriting the original export. Obtain the original CSV again, import the identifier column as Text, and verify the complete strings.

If the source system already supplied rounded numbers, request a fresh export that preserves identifiers as strings. Do not guess suffixes or copy missing digits from an unrelated record. If two distinct IDs collapsed to the same numeric value, the altered workbook alone cannot tell you which was which.

A final handoff check

For this sample, confirm six data records and four columns, then check the exact values above. If you export to CSV, import that exported file again with the recipient's intended settings. CSV carries characters and field boundaries, not Excel column-type metadata.

For shorter codes whose first zeros disappear, read Keep leading zeros when importing a CSV. For garbled names and descriptions, use the text encoding guide.