Excel removes leading zeros. Here is how to keep them.

Why Excel does it

A CSV file has no types: every value is text. When you double-click a CSV, Excel guesses a type for every cell as it imports it — and 00731 looks like the number 731. Numbers have no leading zeros, so they are dropped. The same guess turns 01067 (a postcode in Dresden) into 1067 and 0041 44 123 45 67 into something that is no longer a phone number.

The damage is only on screen until you save. Save the file as CSV from Excel and the zeros are gone from the file too — and with them the match against every other system that uses those numbers.

A code is not a number. Nobody adds up postcodes.

How to keep the zeros in Excel

If you have to use Excel, do not double-click the CSV. Import it instead, and tell Excel which columns are text:

1. Open an empty workbook and choose Data ▸ From Text/CSV (or Get Data ▸ From Text).
2. Choose the file and, in the preview, transform or edit the columns that hold codes.
3. Set their type to Text before loading. Excel then keeps 00731 as it is.

Recent versions of Microsoft 365 also have settings for automatic data conversion, where removing leading zeros can be switched off — on a Mac under Excel ▸ Preferences ▸ Edit, on Windows under File ▸ Options ▸ Data. That helps on your own computer; it does not help a colleague who opens the file with the default settings. (Microsoft: Set automatic data conversions)

What does not really help: typing an apostrophe in front of each value ('00731) changes the data, and formatting a column as Text after the file has opened is too late — the zeros are already gone.

How to avoid it altogether

Open the file with something that does not guess. In Intact CSV Editor a value is kept as the text that was in the file: 00731 is shown as 00731, sorted and filtered as you would expect, and saved back as 00731. It is treated as a number only where you ask for arithmetic — the sum in the status bar, a numeric sort, a SQL query.

When you do want to change the zeros, that is a command rather than an accident: Change the Selected Cells can remove leading zeros or fill values with zeros to a width, and shows the first few cells before and after.

Sending the file to someone who uses Excel

If the file is going to an Excel user, give them a workbook rather than a CSV: export it as Excel .xlsx from Intact CSV Editor. In the workbook, codes are stored as text, so Excel shows 00731 without anyone having to import anything.

The same goes for a few cells pasted into a spreadsheet. Selection ▸ Copy Selection As ▸ HTML Table marks a value with a leading zero, a leading plus or more than fifteen digits as text, so Excel and LibreOffice paste 00731 and +41 as they are. Quantities, dates and percentages still paste as numbers.