Python Excel AutomationPractical guides for developers
Fix
Getting Started With Python Excel AutomationDeep dive

Fix Long ID Numbers Losing Digits or Turning into 1.23E+18 in Excel

Account numbers, card numbers and 18-digit IDs lose their last digits when written to Excel as numbers — Python writers keep 16 significant digits and Excel keeps 15. Write identifiers as text, read them back as text, and stop the leading zeros and trailing digits from changing.
Errors & fixesWriting Excel filesNumber & date formats

A column of account numbers goes into a workbook and comes out different. A 19-digit transaction ID becomes 1.23457E+18; widen the column and it reads 1234567890123456800 — the last digits are gone. A 16-digit card number looks fine until someone edits the file in Excel, after which it ends in 0. And IDs with leading zeros, like 000123, come back from read_excel as 123.

These are three different mechanisms with one cure: an identifier is not a number, so it should never be stored as one. This page, part of Troubleshooting Common Python Excel Errors, shows where each loss happens and how to keep the digits intact on the way in and on the way out.

Where a long ID loses digits Three stages. Python holds the integer 1234567890123456789 exactly. When openpyxl or xlsxwriter writes it as a number, it is formatted with 16 significant digits and becomes 1.234567890123457E+18 in the file. Excel keeps 15 significant digits, so the value it shows ends in zeros. Writing the ID as text skips both losses. Python int 1234567890123456789 — exact Written as a number 16 significant digits → 1.234567890123457E+18 Held by Excel 15 significant digits → …456800 Written as text every digit kept

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter
Python
import re
import zipfile
import pandas as pd
from openpyxl import Workbook, load_workbook

Step 1 — See the loss in the file itself

The most surprising part is that digits can disappear before Excel is involved at all. openpyxl and xlsxwriter both format numbers for the file with Python's %.16g — sixteen significant digits. That is enough for any value a float can hold exactly, but an integer with more digits is rounded as it is written:

Python
ids = pd.DataFrame({"txn_id": [4111111111111111, 1234567890123456789]})
ids.to_excel("ids_as_numbers.xlsx", index=False)

with zipfile.ZipFile("ids_as_numbers.xlsx") as z:
    xml = z.read("xl/worksheets/sheet1.xml").decode()
print(re.findall(r'<c r="A[23]"[^>]*><v>(.*?)</v>', xml))
# ['4111111111111111', '1.234567890123457E+18']

print(pd.read_excel("ids_as_numbers.xlsx")["txn_id"].tolist())
# [4111111111111111, 1234567890123457024]

The 19-digit ID is already wrong in the file: reading it back gives …457024, not …456789. The 16-digit card number survives the write, but it is now at the mercy of the next program that stores it. Excel keeps 15 significant digits — a documented limit of the product — so when Excel takes in that value, for example when someone edits and saves the sheet, the sixteenth digit becomes 0.

Step 2 — Write identifiers as text

Convert ID columns to strings before writing. A Python str becomes a text cell, which Excel stores and displays character for character, however long:

Python
ids = pd.DataFrame({
    "txn_id": [4111111111111111, 1234567890123456789],
    "account": ["000123", "004567"],
})
ids["txn_id"] = ids["txn_id"].astype(str)

ids.to_excel("ids_as_text.xlsx", index=False)
with zipfile.ZipFile("ids_as_text.xlsx") as z:
    xml = z.read("xl/worksheets/sheet1.xml").decode()
print('t="s"' in xml or 't="inlineStr"' in xml)
# True — the cells are text

Convert at the point the data enters your code, not just before writing. If the IDs come from a database or an API as integers, they may already have been through arithmetic-capable types; the moment an 18-digit value is converted to a float anywhere in the pipeline, the digits are gone and no later astype(str) brings them back. Keep them as str (or as Python int, which is exact) from the source.

The same IDs stored as numbers and as text Left: IDs written as numbers. The 16-digit card number survives, but the 19-digit ID is stored as 1.234567890123457E+18. Right: the same IDs written as text keep every digit. A 1 txn_id 2 4111111111111111 3 1.234567890123457E+18 A 1 txn_id 2 4111111111111111 3 1234567890123456789 written as numbers written as text astype(str)

Step 3 — Read them back as text

The reading side has its own trap. pandas' Excel reader infers types for text columns the way its CSV reader does, so a text cell holding 000123 comes back as the integer 123, and very long text IDs can come back as integers too:

Python
back = pd.read_excel("ids_as_text.xlsx")
print(back.dtypes.to_dict(), back["account"].tolist())
# {'txn_id': dtype('int64'), 'account': dtype('int64')} [123, 4567]

Name the identifier columns in dtype and they arrive exactly as stored:

Python
back = pd.read_excel("ids_as_text.xlsx", dtype={"txn_id": str, "account": str})
print(back.to_dict("records"))
# [{'txn_id': '4111111111111111', 'account': '000123'}, {'txn_id': '1234567890123456789', 'account': '004567'}]

dtype=str for the whole frame also works, and is a reasonable choice for staging tables where every column is converted deliberately afterwards. Keep Leading Zeros When Reading Excel with pandas covers the reading options in more detail, including converters.

Step 4 — Protect the column for people who edit the file

Text cells stay text when Excel saves them, so the digits are safe in the cells you wrote. The remaining risk is new rows: a person typing a 16-digit number into an ordinary cell gets a number, and the fifteen-digit limit applies. Give the ID column the Text number format (@) so anything typed there is stored as text too:

Python
wb = load_workbook("ids_as_text.xlsx")
ws = wb.active
for row in range(2, 1001):
    ws.cell(row=row, column=1).number_format = "@"
    ws.cell(row=row, column=2).number_format = "@"
ws.column_dimensions["A"].width = 24
ws.column_dimensions["B"].width = 12
wb.save("ids_protected.xlsx")

print(load_workbook("ids_protected.xlsx").active["A500"].number_format)
# @

Formatting a thousand empty rows is a trade-off: it makes the sheet safer to extend by hand, and it also makes max_row report 1000 in openpyxl — see Fix openpyxl max_row Counting Empty Rows before appending to such a sheet from code. For a form that people fill in, an Excel table (ws.add_table) whose ID column is formatted as text is often a tidier alternative, since new rows inherit the table's formats.

Step 5 — Do not use CSV as the hand-off

A CSV file carries no types, so the digits in it are only as safe as the program that opens it. Python writes them correctly:

Python
ids.to_csv("ids.csv", index=False)
print(open("ids.csv").read())

but a spreadsheet opening the CSV by double-click decides for itself that 1234567890123456789 is a number, and the fifteen-digit rule applies again. If the recipient uses Excel, send an .xlsx with text cells. If it must be CSV, tell the recipient to import it through the spreadsheet's import dialog with the ID column set to Text, rather than opening it directly — the import dialog is the only place a CSV's column types can be chosen. Excel vs CSV vs Parquet for Python Data Pipelines covers the wider trade-off.

Which storage keeps a long identifier intact A grid of storage choices against three risks: digits after the 16th, digits after the 15th, and leading zeros. A number cell loses all three. A text cell keeps all three. A text cell plus the @ number format also protects rows typed later. A CSV opened directly in a spreadsheet loses all three. Digits past 16th Digits past 15th Leading zeros Rows typed later Number cell Text cell Text cell + "@" format CSV opened directly

Which columns count as identifiers?

A useful test: would you ever add, average or sort this value numerically? Account numbers, card numbers, IBANs, postcodes, phone numbers, product codes, national ID numbers and order references fail that test — they are labels made of digits. Quantities, amounts and counts pass it. Write the first group as text and the second as numbers, and state the rule in the job's configuration so it is applied the same way every run. A short list of column names is enough:

Python
ID_COLUMNS = ["txn_id", "account"]

def prepare_for_excel(frame):
    out = frame.copy()
    for col in ID_COLUMNS:
        if col in out.columns:
            out[col] = out[col].map(lambda v: v if isinstance(v, str) else str(v))
    return out

print(prepare_for_excel(pd.DataFrame({"txn_id": [1234567890123456789], "amount": [9.5]})).dtypes.to_dict())

Mapping each value with str rather than calling astype(str) on a float column is deliberate: if a missing value has already turned an integer column into floats, astype(str) gives '1.2345678901234568e+18'. Convert before anything turns the column into floats, and treat a float-typed ID column as a bug upstream.

Common pitfalls

  • Formatting the cell as 0 to hide the E+ notation. It changes the display, not the stored value. The digits beyond the fifteenth or sixteenth are already gone.
  • Converting after a float has touched the column. A NaN in an integer column turns it into floats in pandas; convert IDs to text before any operation that could introduce missing values.
  • Forgetting the reading side. Text in the file is not enough if read_excel converts it back; pass dtype.
  • Silencing the green triangle by converting to numbers. The "Number Stored as Text" indicator is correct for IDs; Fix "Number Stored as Text" Warnings in Excel Output shows how to leave IDs as text while fixing real numbers.

Performance notes

Text cells cost slightly more space than numbers, because each distinct string goes into the workbook's shared-strings table. For a million unique 19-digit IDs that is a few megabytes, and it is still the only representation that keeps the value. Reading with dtype=str is marginally faster than letting pandas infer types, since the inference step is skipped.

Conclusion

Long identifiers lose digits in two places: Python's Excel writers keep 16 significant digits, and Excel itself keeps 15. Leading zeros are lost a third way, when read_excel infers numbers from text. Store every identifier as text from the moment it enters your code, write it as text, read it back with dtype=str, format the column as @ for rows people will type, and hand files over as .xlsx rather than CSV.

Frequently asked questions

Why does Excel change the last digit of my 16-digit number to 0? Excel stores numbers with 15 significant digits of precision. Any digits after the fifteenth are replaced with zeros when Excel takes in the value, so a 16-digit card number ends in 0. Store identifiers as text instead.

Why did the digits change before I even opened the file? openpyxl and xlsxwriter write numbers with 16 significant digits. An integer with 17 or more digits is written in scientific notation with the tail rounded away, so the file itself no longer contains the original value.

Will writing IDs as text cause "Number Stored as Text" warnings? Excel shows its small green triangle on text that looks like a number. For identifiers that is correct and harmless; the warning can be turned off per range. Treating IDs as numbers is the thing to avoid.

How do I read the IDs back without losing leading zeros? Pass dtype={"id_column": str} to read_excel. Without it, pandas converts text that looks numeric back into integers, which drops leading zeros and can lose precision on very long values.

More on errors & fixes

More on errors & fixes →