How to Preserve Leading Zeros in Excel and CSV Files

You open a CSV file in Excel and a postcode that was 02134 now reads 2134. A barcode shows as 4.0064E+12. Nothing was wrong with the file. Excel read each value as a number and displayed it the way numbers are normally displayed.

Why leading zeros disappear

A CSV file is plain text. It has no way to say “this column is text, not numbers”. When Excel opens one directly, it decides for itself how to interpret each column, and anything made only of digits looks like a number. Numbers do not have leading zeros, so they are dropped.

Microsoft documents this behaviour: Excel removes leading zeros and shows large numbers in scientific notation, and it has a maximum precision of 15 significant digits (Keep leading zeros and large numbers, Excel specifications and limits).

That is fine for amounts and quantities. It is wrong for identifiers, where 00123 and 123 are different codes and nobody does arithmetic on them.

The safest way to open a CSV in Excel

Do not double-click the file. Import it, and tell Excel which columns are text before loading. The menu names below follow Microsoft’s documentation, which lists Microsoft 365 and Excel 2016 or later. Labels differ a little between versions, so use them as a guide.

1. Open a blank workbook. On the Data tab choose From Text/CSV.
2. Select your file and choose Import.
3. In the preview, choose Transform Data (some versions label this Edit) to open the Power Query editor.
4. Click the header of each identifier column (hold Ctrl to select several). Then go to Home → Transform → Data Type → Text.
5. If asked in the Change Column Type dialog, choose Replace Current.
6. Choose Close & Load. The identifiers arrive as text, zeros intact.

Sources: Keep leading zeros and large numbers and Import or export text (.txt or .csv) files.

Newer Excel builds also have an Automatic Data Conversion section (File → Options → Data on Windows). It can stop “Remove leading zeros and convert to number” and “Keep the first 15 digits of long numbers and display in scientific notation” for files you open normally. Microsoft states these options don’t directly affect data imported with Power Query, and that they exist only in newer versions (Set automatic data conversions).

Typing an apostrophe before a value, or formatting cells as Text, works for values you enter afterwards. Neither brings back zeros that were already removed.

How to fix an identifier column

If zeros are already gone, you can add them back when you know the correct length. A basic US ZIP code is five digits, so 2134 can safely become 02134. A product code that is “six characters” in your system works the same way.

The length has to come from you or from a reliable rule. It cannot be inferred from the data: a column containing 123, 4567 and 89 could be 4, 6 or 10 characters wide. Guessing would invent values that never existed.

Scientific notation and long numbers

Excel stores 15 significant digits. In a number with more digits, everything after the fifteenth is replaced by zeros (Microsoft’s guidance). Credit-card numbers, long account numbers and some barcodes are affected.

Two situations look the same on screen but are very different:

• Short and exact. 1.2345E+12 stands for 1234500000000, a whole number of 13 digits. Nothing has been lost, so it can be written out in full.
• Long and already rounded. 1.23456789012346E+17 has more than 15 digits. If it was saved from a spreadsheet, the original digits may have been replaced by zeros. The true value is no longer in the file, and no tool can reconstruct it. Go back to the source system.

How to protect the original data

• Keep the original file untouched, and work on a copy.
• Do not open a CSV in Excel and save over it. Whatever Excel changed on screen is then written into the file.
• Review every change before it is applied, and check the exported file afterwards.
• Remember that quote marks in a CSV only protect commas and line breaks. They are not a data type, so a quoted "00123" is still just text that Excel may choose to read as a number.

Common mistakes

• Guessing the length. Padding “everything to 10” because it looks tidy changes identifiers that were never 10 characters.
• Changing the extension. Renaming a file to .csv does not preserve anything. The values inside are what matter, and they may already be damaged.
• Formatting as Text after the fact. It prevents new damage, not the damage already done.
• Treating every long number as an ID. Amounts and measurements should stay numbers.

Doing it with Riluko

The CSV Cleaner’s Protect identifiers step works in your browser. Your file is not uploaded and the source file is never changed.

• It highlights columns where zeros look lost or values look like scientific notation.
• It adds leading zeros to a column when you enter the length. Values that are longer, blank or contain letters and punctuation are left alone and listed for review.
• It writes scientific-notation values out in full only when the result is exact (up to 15 digits), and flags the rest as possibly already rounded.
• You see a preview first, and you can undo and download the result as a CSV.

It cannot know the right length by itself, and it cannot recover digits that no longer exist.

Frequently asked questions

Can zeros be restored automatically?

Only when the intended length (or a rule) is known. Without it, any number of zeros would be a guess, so Riluko asks you for the length.

Does saving as “CSV UTF-8” keep my leading zeros?

The encoding affects letters and symbols, not whether digits are read as numbers. Zeros that are still in the file stay there, but Excel can remove them again the next time the file is opened directly.

Why does a barcode show as 4.0064E+12?

Excel has displayed a number with many digits in scientific notation. If the value has 15 digits or fewer, the full number can be recovered. With more, check whether the original digits still exist.

Is my data sent anywhere?

No. The CSV Cleaner processes the file in your browser. See the privacy page for details.