Keep leading zeros and long IDs in Excel
September 25, 2026 · By TidyKit
An order ID is a label, even when every character is a digit. Keep it as text from the first import. Treat prices and quantities as numbers in separate columns.
Keep the original file. Identify code columns. Import those columns as Text, then check the first, middle and last records against the source.
Two different ways a code can break
| Original value | What to check | Intended type |
|---|---|---|
00127 | All five characters remain, including both zeros | Text: product code |
12345678901234567 | All 17 digits match, especially the ending 567 | Text: record ID |
24.50 | The amount can be used in a calculation | Number: price |
Excel can drop initial zeros when it converts text to a number. It also stores only 15 significant digits in numeric cells. Formatting a damaged ID as Text later does not recover it. Return to the original source. Microsoft explains both limits.
Import a CSV with the ID column set to Text
- In Excel, choose Data > From Text/CSV and select the original file.
- Open the transformation editor instead of loading immediately.
- Set each ID column to Text. Replace any earlier automatic type conversion for that column; inspect the preview before loading.
- Load the result and compare a few complete IDs with the source.
Names of the import controls vary by Excel version. See Microsoft's import instructions. For a new manually entered ID, format the destination cell as Text first.
A display format is not a repair
A fixed-width format can display 127 as 00127. It does not tell you whether the original code was 0127 or 00127. Do not guess the missing digits from appearance alone.
For a table on a website, choose XLSX
TidyKit Table to Excel's quick XLSX export keeps digit strings with leading zeros and numbers longer than 15 digits as text. Copy and CSV leave interpretation to the receiving spreadsheet. The extension cannot recover digits already missing from the web page.
Use the example table above as a small check: export it as XLSX and inspect the two IDs before exporting your own data. Read how Table to Excel works.
Check more than the first row
Try a short code, a code starting with zero and the longest ID you have. If another person will reopen a CSV, tell them which columns need Text. A correct-looking workbook is not proof that a later import will use the same types.