The immediate fix: Don't double-click the CSV file. Instead, open Excel, go to Data → From Text/CSV, browse to your file, and when the import wizard opens, click on the problematic column and change the data type from "General" to Text. Click Load. Your leading zeros survive.
If you've already opened the file and lost the zeros, read on — there's a recovery method.
Why This Happens
Excel treats every column as "General" format when you open a CSV. For General columns, it auto-detects the data type: if a value looks like a number, Excel converts it to a number — and numbers don't have leading zeros. So 02134 becomes 2134, +0044 7700 900077 becomes 447700900077, and 007 becomes 7.
This isn't a bug; it's Excel doing what it was designed to do. The problem is that ZIP codes, phone numbers, product SKUs, and employee IDs are not numbers — they're identifiers that happen to contain digits, and their leading zeros are meaningful.
All the Fixes, Ranked
Fix 1: Import Wizard (Best — Prevents the Problem)
Works when opening a CSV file for the first time.
Sohovi gives you a full quality report on any spreadsheet in seconds — upload your file and see exactly what needs fixing.
- Open Excel (don't open the CSV by double-clicking)
- Data → From Text/CSV → browse to your file
- In the preview dialog, click on the column(s) with leading zeros
- Change the type dropdown from "General" to Text
- Load
This is the correct fix. The leading zeros are preserved because Excel never attempts to interpret the column as a number.
Fix 2: Format as Text Before Pasting
If you're pasting data rather than importing a file:
- Select the destination column
- Right-click → Format Cells → Number tab → select Text → OK
- Now paste your data
The column is formatted as text before data arrives — Excel won't attempt number conversion.
Limitation: This only works if you format BEFORE pasting. If you paste first (losing zeros) and then format as text, the zeros are already gone — formatting after the fact doesn't restore them.
Fix 3: Leading Apostrophe
Type or paste an apostrophe before the value: '02134
Excel treats any value starting with an apostrophe as text, preserving it exactly as typed. The apostrophe itself is not visible in the cell — only in the formula bar.
Limitation: The apostrophe is stored in the cell's raw value. If you export this data back to CSV and another system processes it, the apostrophe may appear as a character. Not suitable for data you'll export and re-import.
Fix 4: Custom Format "00000" (Display Only)
Format cells with a custom number format like 00000 for 5-digit ZIP codes.
- Select the column
- Right-click → Format Cells → Number tab → Custom
- Type:
00000(or however many digits your field requires) - OK
This displays leading zeros visually — 2134 shows as 02134 — but the underlying cell value is still the number 2134. If you export to CSV, you'll get 2134, not 02134.
Use only for display purposes. Not suitable if the leading zero needs to survive an export.
Fix 5: TEXT() Formula
=TEXT(A2, "00000") converts the number in A2 to a text string "02134".
Useful if you have a column of stripped numbers and need to reconstruct the leading-zero versions. Paste the formula results as Values Only (Paste Special → Values) before deleting the source column.
Recovering Lost Leading Zeros
If you've already opened a file and the zeros are gone:
- Identify how many digits the field should have (ZIP = 5, certain phone formats = 10, etc.)
- Create a helper column:
=TEXT(A2, "00000")substituting the correct number of zeros - Paste that helper column back as values
- Delete the original column
This only works if you know the correct length. If your IDs should all be 8 digits and some are 6 or 7 — because they originally had 2 or 1 leading zeros — you can't reliably reconstruct them.
The Real Problem: It Happens Every Time
The fixes above solve the problem for this file, this time. If you receive a weekly CRM export with ZIP codes and phone numbers, you'll need to apply the import wizard fix every single week. That's how recurring bad data works — the fix is never permanent unless you change the process.
Sohovi finds gaps, duplicates, and format errors in your CRM data — so your team is working from records they can trust.
For repeatable workflows, set a validation rule in Sohovi: "this column must be 5-character text starting with a digit." When a file arrives and leading zeros are already stripped, the rule flags it immediately — before you process or report on the data. One rule, set once, runs every time.
Frequently Asked Questions
Q: Why does formatting a cell as Text after pasting not restore the leading zero? Because formatting controls how Excel displays a value, not what the value is. When Excel converted 02134 to the number 2134 on import, the leading zero was discarded from the stored value. Changing the format to Text afterwards just tells Excel to display the number 2134 as text — it still shows 2134, not 02134.
Q: How do I prevent leading zero loss in Google Sheets? In Google Sheets, format the column as Plain text (Format → Number → Plain text) before pasting. Or use the import feature (File → Import) and set the column type to Text. The underlying issue is the same as Excel.
Q: Does this happen with phone numbers and international codes? Yes. Country codes like +44 get stripped of the plus sign, and numbers beginning with 0 (UK local numbers, many Asian formats) lose their leading zeros. The fix is the same: import as text or format before pasting.
Q: My CSV file looks correct in a text editor but wrong when I open it in Excel — why? Because CSV files are plain text — they store 02134 correctly. Excel's type-inference on open is the problem. The data was fine; Excel changed it. Use the import wizard (Data → From Text/CSV) and specify text columns explicitly.
Q: Is there a way to make Excel stop guessing column types on CSV open? Not globally — Excel always auto-detects types on direct open. The import wizard is the only way to control type assignment per column.
Stop re-fixing the same file every week. Set a validation rule in Sohovi that flags leading-zero loss the moment a file arrives — so you catch the problem before it reaches your reports or campaigns.