Excel Changed My CSV Numbers: Keep Leading Zeros and Long IDs

You open a CSV file in Excel. A product code changes from 00127 to 127. A long order ID looks different too. You widen the column, but the missing digits do not come back.

The problem is often how Excel reads the column. It may treat an ID as a number for calculations, even though you need every character kept exactly as written.

The safest starting point is simple: keep the original file, then import a new copy with ID columns set to Text. The steps below focus on desktop Excel. Menus can differ between Windows, Mac and Excel versions.

1. Check whether the original file still has the right values

Do not save over the only CSV you have. Make a copy first.

Open the original in a plain text editor, such as Notepad. Find one value that looks wrong in Excel. Check its beginning, ending and total length.

There are two different situations:

• The CSV is correct, but Excel looks wrong: import it again with the right column type.
• The CSV already has missing or changed digits: get a fresh export or a known-good backup. A display setting cannot recover information missing from the source.

This check can save you from repairing the wrong file. For example, if Notepad shows 00127 but Excel shows 127, you have a useful reference for a new import. If both show 127, first ask whether the source system exported the code correctly.

2. Decide which columns are labels and which are amounts

Ask one question: Would adding these values together make sense?

Adding two sales amounts makes sense. Adding two customer IDs does not. Phone numbers, postal codes, order references and product codes usually act as labels, even when they contain only digits.

Write down the column types before importing:

• Product code: Text.
• Customer ID: Text.
• Phone number: Text.
• Quantity: a suitable number type.
• Sale amount: a suitable number type, with the correct decimal rules.

Microsoft explains that Excel can remove starting zeros and that numbers have a maximum precision of 15 significant digits. Treat long identifiers as text before conversion. See Microsoft's guide to leading zeros and large numbers.

Making every column Text is not always the best choice. You may still need quantities and amounts for calculations. Keep labels as text and choose numeric types only where they serve the task.

3. Import the CSV instead of double-clicking it

Use a blank workbook and follow this workflow:

1. Open the Data tab.
2. Choose From Text/CSV and select your copied file.
3. Check the preview. Confirm that commas or another separator split the fields correctly.
4. Open Transform Data, or the edit option shown in your version.
5. Select each ID or code column and set its data type to Text.
6. If asked whether to replace the current conversion, choose the option that replaces it.
7. Check the preview against your original values, then choose Close & Load.

These steps use Excel's Power Query import tools. Microsoft's CSV import and export guide explains the available methods. Older interfaces may offer the Text Import Wizard instead. Its column-format step also lets you choose Text.

Do not continue if the preview is already wrong. Revisit the earlier conversion step or start a fresh import. Turning a value into text after it has been shortened only turns the shortened value into text.

Try this small practice file first

Download our CSV import practice file. It contains fictional values and no customer information:

product_code,customer_id,quantity
00127,123456789012345678,2
00008,987654321098765432,5

For this exercise, use Text for the first two columns and a number type for quantity. After import, check three things:

• The first product code is still 00127, including both starting zeros.
• The first customer ID still ends in 678 and has all 18 digits.
• The two quantities can be added to give 7.

The aim is not just to make the sheet look right. It is to preserve the two labels while keeping the quantity useful for calculation. This is a practice example, not a claim that we tested every Excel version.

4. Save a working workbook, then check any export

Save your working copy as XLSX if you need to keep workbook features and cell types. Keep the original CSV separately.

A CSV does not carry Excel's cell-format settings. If you export another CSV and later double-click it, Excel may interpret its values again. Check the exported file in a text editor before sending it to another system. The receiving system also needs the correct field types.

For a handoff, include a short note such as: “Import product_code and customer_id as text. Quantity is numeric.” This tells the next person what your file alone cannot explain.

If you build exports yourself, our Next.js CSV download guide covers the download response. A working download and a correct spreadsheet import are separate checks.

5. Avoid fixes that only change the appearance

A custom format such as 00000 can display a known five-digit code with zeros. It does not prove what the original code was, and it cannot restore an unknown ending that was lost from a long ID.

Use the original data as your reference. Do not guess that every short code needs the same number of zeros. A list may contain codes of different lengths.

Some newer Excel versions also have Automatic Data Conversions controls. These can help prevent unwanted changes, but availability differs by version. Check Microsoft's data import options before relying on that setting. You can still use an explicit import workflow to show how each column should be read.

A quick check before you send the file

Compare one value with starting zeros, one long ID and one normal amount. Check a row near the end as well as the first row. Keep the source, working workbook and final export clearly named.

Once those checks pass, you have evidence that the file suits its next use. You have not merely made one cell look correct.