Forum Discussion

SelinaKnow's avatar
SelinaKnow
Iron Contributor
Nov 09, 2026

How do you stop Excel from removing leading zeros?

When opening a CSV in Excel, product codes such as “00125” can become “125.” This causes problems when matching them against the original records.

What’s the simplest way to preserve leading zeros, especially when sharing the CSV with colleagues who open it by double-clicking?

3 Replies

  • The part that catches most people here is the timing. Once a CSV has been opened by double-clicking it, Excel has already decided 00125 is the number 125, and the zeros are gone from the file as Excel sees it. Formatting those cells as Text afterwards won't bring them back, because there's nothing left to format — you'd have to close without saving and import it again properly.

     

    For sharing, that's why the file format matters more than any setting on your own PC. A CSV stores no formatting at all, so each colleague who double-clicks it gets the same conversion. If you send the same data as a workbook instead, with the product-code column already set to Text before you save it, the codes arrive intact. Per Microsoft Support's automatic data conversions documentation, the newer Excel option to stop removing leading zeros is a setting on each person's own copy of Excel, so it only helps colleagues who have turned it on themselves. For everyone else, importing through Data, Get Data, From Text/CSV and setting that column to Text, or just sending a workbook, is the reliable route.

  • Terio's avatar
    Terio
    Brass Contributor

    When you're importing csv files, use the format text for this fields or, better, use Power Query to import and transform datas.

    Bye

  • Here's the full article how to stop automatic data conversations when opening CSV-files:

    https://support.microsoft.com/en-us/excel/set-automatic-data-conversions

    Note: The option to stop this conversation is only availbe in Excel 2024 and above!

    Do not share CSV files with people who do not understand to import data correctly, share the data as Excel file. In previous versions you must import the CSV file using Data \ From File \ From CSV and transform the data to preserve the numbers as text.

    Andreas.