Why leading zeros matter
Leading zeros are critical for business data. They keep IDs and codes consistent, sortable, and recognizable. But they disappear when data is treated as numbers instead of text.
Customer IDs become unrecognizable
Customer ID 00847 becomes 847. Your team can't find them in the system.
Account numbers don't match
Account 0095 becomes 95. It won't match your accounting software.
SKU lookups fail
Product SKU 00412 becomes 412. Inventory system can't find it.
Postal codes and area codes break
ZIP code 00501 becomes 501. Shipping software can't route the order.
Fixed-length codes change length
00847 and 01045 become 847 and 1045, so codes that should all be five digits fail length checks and no longer line up.
Data becomes unmappable
00847 in CSV ≠ 847 in your database. No way to match them.
Real example:
What your data should be:
Customer ID | Name | Account ------------|----------------|------- 00847 | Smith Corp | 0095 00923 | Jones Inc | 0102 01045 | Brown Ltd | 0156
What Excel does (WRONG):
Customer ID | Name | Account ------------|----------------|------- 847 | Smith Corp | 95 923 | Jones Inc | 102 1045 | Brown Ltd | 156
❌ Leading zeros stripped—IDs no longer match your system
Root cause: Excel treats numbers as numbers, not text. It automatically strips leading zeros because "00847" and "847" are mathematically identical. But for business data, they're different.
Common data types that lose leading zeros
Customer/Account IDs
Examples:
00847, 00923, 01045
Impact when lost:
Can't find customers, duplicate entries created
Product SKUs
Examples:
00412, 00413, 01020
Impact when lost:
Inventory lookups fail, wrong products shipped
ZIP/Postal Codes
Examples:
00501 (NY), 00602 (PR), 01001 (MA)
Impact when lost:
Addresses don't geocode, shipping routes fail
Phone numbers
Examples:
07700 900123 (UK mobile), 0412 345 678 (Australia)
Impact when lost:
The leading 0 goes, so numbers don't dial or validate
Invoice/Reference Numbers
Examples:
00001, 00002, 00100
Impact when lost:
Can't match invoices to accounting software
Employee IDs
Examples:
00001, 00042, 00156
Impact when lost:
Other systems can't find employees
Lab/Report Numbers
Examples:
00001-LAB, 00002-TEST
Impact when lost:
Medical/scientific results become unsearchable
Why Excel strips leading zeros
Excel automatically interprets data as numbers when possible. Mathematically, 00847 = 847, so once Excel reads it as a number, the zeros have nowhere to go.
The Problem: Number Format vs Text Format
Excel is designed for math, not business IDs. So it assumes any numeric-looking value should be treated as a number. The apostrophe tells it to keep the value as text; it isn't shown in the cell or saved in the CSV. Typing quotes doesn't work: they become part of the value.
The Cascade: Spreadsheet → CSV → Import
- You paste customer ID 00847 into Excel
- Excel strips the leading zeros → 847
- You save as CSV
- CSV contains: 847 (no leading zeros)
- You import into your system
- System receives: 847 (can't match 00847)
Once the CSV is saved, the zeros are gone from the file. For codes that are always the same length you can add them back (step 6); otherwise you need the original data.
How to fix and prevent leading zero loss
Step 1: Don't open the file in Excel unless you need to
Best approach: If you're only moving a CSV from one system to another, upload the file as it is. Double-clicking it in Excel is what strips the zeros.
To check it: open the CSV in a text editor instead (see step 5).
Step 2: In Excel, set the column to Text first
Typing or pasting:
- Select the column with leading zeros
- Right-click → Format Cells
- Choose "Text" from the list
- THEN type or paste your data
Opening a CSV: use Data → From Text/CSV, click Transform Data, set the ID columns to Text, then Close & Load.
⚠️ Important: Format the column BEFORE pasting. If you paste first, then format, it's too late—the zeros are already gone.
Step 3: Switch off zero-stripping (Excel for Microsoft 365)
Recent versions of Excel let you turn the conversion off: go to File → Options → Data, and under Automatic data conversion untick "Remove leading zeros and convert to number format". The setting is per computer, so anyone else opening the file still needs steps 1 or 2.
Step 4: Work in .xlsx, save the CSV last
A workbook remembers which columns are Text. A CSV doesn't, so Excel guesses again every time it's opened. Keep your working copy as .xlsx and use File → Save As → CSV UTF-8 only when you're ready to import.
Step 5: Verify in a text editor
Before importing, open the CSV in a text editor (VS Code, Notepad++) to verify leading zeros are there:
✓ If you see 00847, 00923, 01045 → Good, leading zeros are preserved
❌ If you see 847, 923, 1045 → Bad, they're already lost
Step 6: If leading zeros are already lost
If every code should be the same length, you can restore them:
- Open the CSV in Excel
- Add a helper column with formula:
=TEXT(A1,"00000") - This adds back the leading zeros (adjust 00000 for your ID length)
- Copy the helper column, Paste Special → Values
- Replace the original column
- Delete the helper column
- Save as CSV
This only works for fixed-length codes. If they vary (847 could have been 0847 or 00847), go back to the original data.
Best practices for preserving leading zeros
Related CSV topics:
Prevent ID corruption at the source
Collect data through forms instead of spreadsheets and IDs are never corrupted. No Excel stripping zeros, no manual formatting—data stays clean from collection to export.
Learn more about Import Builder