Stop Excel Changing Numbers in CSV Files

You open a CSV in Excel and a column changes before you touch it. 00127 becomes 127. A 16-digit identifier ends in zeros. 3-5 appears as a date.

The CSV may still be correct. Excel has interpreted the text while opening it.

That distinction matters because some changes affect only how a cell is displayed, while others alter the stored value. Widening a column can reveal a number hidden behind scientific notation. It cannot restore digits that Excel has already replaced.

First, do not save over the source file

If the file is still open and the values look wrong, close it without saving. Make a copy of the original before trying again.

Opening a CSV is not always harmless in Excel. Saving the workbook back to CSV serializes Excel's interpreted values. Once that overwrites the original, the lost characters may be unrecoverable without another copy of the data.

What Excel changes and why

CSV contains text. It does not label a column as “identifier,” “date,” or “decimal.” Excel therefore guesses when it opens the file.

Leading zeros disappear

Values such as postal codes, product codes, case numbers, and staff IDs may contain only digits but still be text.

00127  →  127

Once Excel treats the value as a number, leading zeros have no numerical meaning and are removed.

Long identifiers lose precision

Excel stores ordinary numbers with a maximum precision of 15 significant digits. Microsoft documents this limit in its Excel specifications.

12345678901234567  →  12345678901234500

Changing the cell format to Text after import does not bring the final digits back. The value has already been rounded.

Codes and labels become dates

Values such as 3-5, SEP1, or 1/2 can resemble dates to Excel. The interpretation depends on the Excel version, locale, and automatic-conversion settings.

This is especially damaging in scientific names, stock codes, version labels, and category fields where a date was never intended.

Scientific notation may be only a display choice

A value displayed as 1.23457E+11 may still be stored accurately if it has no more than 15 significant digits. Select the cell and inspect the formula bar, or widen the column.

For identifiers longer than 15 digits, assume there is a data-loss risk and compare with the original text.

The reliable approach: import the CSV

Do not double-click an important CSV. Start with a blank workbook and import it with control over column types.

Excel for Microsoft 365 or recent desktop Excel

1. Open a blank workbook.
2. Go to Data → From Text/CSV.
3. Select the source file.
4. Check the delimiter and file origin in the preview.
5. Choose Transform Data rather than loading immediately.
6. In Power Query, select every identifier column.
7. Set its data type to Text.
8. Review date and decimal columns, using the correct locale where necessary.
9. Choose Close & Load.

Microsoft's CSV import guidance recommends the import route when you need control over column formats, including columns with leading zeros.

Power Query may add a Changed Type step automatically. Inspect it. If it assigned a number or date type to an identifier, remove that step or change the affected column to Text before loading.

Using the legacy Text Import Wizard

Some Excel installations still expose From Text (Legacy). If it is unavailable, Microsoft explains how to enable the legacy wizard under File → Options → Data.

In the wizard:

1. choose the correct delimiter;
2. select each identifier column in the preview; and
3. set its column data format to Text.

The current Power Query route is usually easier for repeatable work, but either method can preserve text when configured correctly.

Recent Excel versions include conversion controls

Some Microsoft 365 builds provide Automatic Data Conversion settings under Excel's data options. These can stop removal of leading zeros, conversion of long numbers to scientific notation, and some letter-number conversions.

The availability and wording depend on the installed version. Treat these settings as useful protection, not as a substitute for reviewing column types during an important import. Microsoft describes the controls in Data import and analysis options.

Things that do not reliably solve the problem

Quoting the CSV field

This is valid CSV:

name,staff_id
Ada,"00127"

The quotes tell a CSV parser that the field boundaries are protected. They do not universally tell Excel to preserve the value as text. Excel may still import 00127 as a number.

Formatting the column after opening

Changing a cell to Text after Excel has removed zeros or rounded a long number preserves the already-changed value. It does not reconstruct the original.

Prefixing values with an apostrophe

Typing '00127 directly into an Excel cell is a familiar way to enter text. Placing the apostrophe in a CSV changes the file's data: other systems may receive the apostrophe as a literal character. It is not a clean interchange solution.

Adding formulas such as ="00127"

That may make Excel display the intended text, but the CSV now contains a formula rather than the original value. It can break imports and create spreadsheet-formula security concerns. Do not use it for files distributed to others.

If the file has already been saved

Recovery depends on what changed.

• Display-only scientific notation: widen the column or apply a different number format.
• Known-width codes: if every code should have the same length and the underlying number is intact, a formula such as =TEXT(A2,"00000") can reconstruct the padding.
• Rounded values longer than 15 digits: obtain the original export or restore a previous version. The lost digits cannot be inferred safely.
• Codes converted to dates: return to the original source. Reversing the displayed date does not reliably reveal the exact source text.

Check version history in OneDrive, SharePoint, your backup system, or the application that generated the export.

Saving after a safe import

If you need to work on the data in Excel, save a working copy as .xlsx. That format keeps the column types, formulas, and workbook settings.

Export a new CSV only when another system requires it. Then reopen the exported CSV in a plain-text viewer or CSV Viewer and verify a few high-risk fields against the original:

• the first and last identifier;
• a value with leading zeros;
• a value longer than 15 digits;
• a date near the start of a month; and
• a non-ASCII name.

A safer rule for future files

Decide which columns are identifiers before opening the CSV. Import those columns as Text, keep the untouched source, and validate the exported copy.

Excel is useful for analysing CSV data. It simply cannot know that 00127 is an identity rather than a quantity unless you tell it.