Leading zeros disappear in Excel: account, customer and phone numbers, fixed

Convert to .xlsx and the zeros stay

The converter tells numbers from codes before it writes the file. A value that starts with 0, or that is a long run of digits, stays text: customer numbers, article numbers, phone numbers, IBANs, card numbers, short dates like 01.10. Amounts become numbers. In the .xlsx, each cell carries its type, so Excel has nothing to guess.

What we measured: .xlsx against CSV

We converted an invented customer list for Jane Cooper, Karol Michał and Jana Čermák, with customer numbers, phone numbers, IBANs and card numbers, to both .xlsx and CSV, and opened both in Excel set to English (US):

The CSV itself was right: it held 004711, every digit. Excel changed the values while opening it. Values with spaces, a plus sign or letters survived, because Excel cannot read them as numbers. So when a sheet shows 4711, the conversion was rarely the culprit; the step that opened a CSV or pasted text was.

Prevent it: bring codes in as text

Use the .xlsx. It is the default format, and it opened with every code intact.

If you need CSV, do not double-click it. Import it with the code columns typed as text. Microsoft’s help “Keeping leading zeros and large numbers” (checked on 11 October 2026) gives these steps:

1. Data, then From Text/CSV, and pick the file.
2. Click Transform Data instead of Load.
3. Click the code column’s heading, then Home, Transform, Data Type, and select Text. Choose Replace Current when asked.
4. Close & Load.

We did the equivalent import with every column as text: 004711, 000815, 06601234567 and the 16-digit card number all arrived unchanged.

Typing or pasting by hand? Format the column as Text first (select it, Ctrl+1, Number tab, Text), then paste. Or type an apostrophe before a single value, as in ’004711; Excel stores it as text.

Excel for Microsoft 365 or 2024 can stop converting altogether: File, Options, Data, then under Automatic Data Conversion untick “Remove leading zeros and convert to number” and “Keep the first 15 digits of long numbers and display in scientific notation if required”. On a Mac it is Excel, Preferences, Edit. Microsoft’s help “Set automatic data conversions” (checked on 11 October 2026) lists these, and notes they apply when Excel opens a .csv file.

Get the zeros back after the fact

If the zeros are already gone, there are 2 tools, and they do different things:

• =TEXT(A2,"000000") in a new column gives “004711” as real text. Use as many zeros as the codes have digits. Copy the column and Paste Special, Values over the original if you need the text itself, for a lookup or an import.
• A custom number format shows the zeros without changing the value: select the column, Ctrl+1, Custom, type 000000. The cell shows 004711 but still holds 4711, so a lookup against “004711” as text fails.

Both need every code to have the same length. If some codes had 1 zero and some had 2, the sheet no longer knows which; go back to the PDF and convert again to .xlsx. A card number that lost its 16th digit cannot be repaired from the sheet at all.

The same problem in Google Sheets

Importing the .xlsx into Google Sheets is the safe route, because the codes are text cells in the file. If a column already lost its zeros there, Format, Number, Custom number format with 000000 shows them again; Google’s help on number formats says the 0 symbol keeps zeros that would otherwise be dropped (checked on 11 October 2026). The full import route is in PDF table to Google Sheets.

The honest part

The converter decides by rule, not by meaning. Anything that starts with 0, or has more than 15 digits, stays text; a 6-digit customer number without a leading zero, like 123456, becomes a number, because nothing distinguishes it from an amount. That number is not damaged, but if your codes must stay text for a lookup, format that column as Text after converting. And the zeros problem has cousins: amounts with decimal commas read wrong and dates swapped between day and month. Those are in PDF to Excel number formats; the CSV side, for accounting imports, is in PDF to CSV for accounting.