Excel Ate My Leading Zeros: Keeping SSNs, ZIP Codes, IDs and Dates Intact

If you collect data from clients, you’ve seen it: a ZIP code of 2134, a Social Security number with eight digits, a member ID ending in 000, a column of numbers like 46113 where dates should be. Nobody typed those. Excel did, the moment someone double-clicked a CSV.

What Excel does to your data

Excel looks at every cell and guesses what it is. If it looks like a number, it becomes one:

• Leading zeros disappear. ZIP code 02134 becomes 2134. SSN 078-05-1120 typed without dashes becomes 78051120. Account and employee numbers lose their zeros too.
• Long numbers lose digits. Excel keeps 15 digits of precision. A 16-digit card or policy number becomes 1.23457E+15 on screen, and the last digit is gone for good.
• Codes become dates. “3-4”, “MAR1” or “1/2” are read as dates, and then stored as Excel’s date number: days since December 30, 1899. That’s where 46113 comes from (April 1, 2026).

None of this shows until someone else opens the file.

How to stop it

• Import, don’t double-click. In Excel, use Data, From Text/CSV, choose Transform Data, and set the ID, ZIP, SSN and code columns to Text before loading.
• Format as Text first. Before typing or pasting IDs, select the column and format it as Text. Microsoft’s own advice for stopping numbers from turning into dates is the same.
• Turn the conversions off. In Excel for Microsoft 365 (Windows version 2309 or later, Mac 16.77 or later), File, Options, Data, Automatic Data Conversion lets you switch off removing leading zeros, scientific notation and turning text into dates. Some installations don’t show the setting.
• One cell at a time: start it with an apostrophe ('02134). The apostrophe isn’t part of the value.

How to repair a file that’s already damaged

You can’t always get the original back, but most damage is predictable:

• SSNs with 7 or 8 digits almost always lost leading zeros: pad to 9.
• ZIP codes with 3 or 4 digits: pad to 5 (New England, New Jersey and Puerto Rico ZIPs start with 0).
• Date numbers between about 10000 and 60000 in a date column are Excel dates: convert them.
• Two-digit years need a rule: a birth date in “58” is 1958; a hire date in “04” is 2004.
• Long IDs in scientific notation can’t be repaired: ask for the original.

The census portal example does all of this when a client sends a spreadsheet, and lists every repair so nothing changes silently. What goes wrong in a 401(k) census file covers the rest of what we see in real files.

Key takeaways

• Excel guesses types; IDs and codes aren’t numbers, so tell it.
• Import CSVs with Data, From Text/CSV and set ID columns to Text.
• Leading zeros and date numbers can be repaired; digits past the fifteenth can’t.
• If you collect files from clients, repair the predictable damage automatically and show what you changed.

Drop a messy spreadsheet into the example →

Collecting spreadsheets from your clients? We build portals that take the file as it is and hand you clean data, and because we work with AI-assisted development, it costs far less than you might expect. Let’s talk.