Guide
How to keep leading zeros in product codes in Excel, CSV files and Google Sheets
A supplier sends a CSV. You double-click it, and every code that started with a zero has lost it: 00123 is now 123, and a 13-digit barcode reads 1.23457E+12. Save the file and the damage is permanent. Match that list against last month's list and half the products no longer line up.
This happens because a CSV has no cell types. Everything is text, and a spreadsheet program guesses what each value is. Digits look like a number, so they become a number, and a number has no leading zeros. Here is how to stop the guess in each program, and the one habit that avoids the problem entirely.
The rule that prevents it
Never open a supplier CSV by double-clicking it if it has product codes, barcodes or account numbers. Import it instead, and tell the program the code column is text. Once a zero is gone, no format can bring it back: Microsoft's own guidance says that applying the Text format afterwards "will only affect numbers that are entered after the format is applied."
Excel: turn off the automatic conversion (Microsoft 365 and Excel 2024)
Newer versions of Excel let you switch off the conversions that cause this. Go to File, Options, Data, Automatic Data Conversion. On a Mac it is Excel, Preferences, Edit, Automatic Data Conversion. Untick the options that remove leading zeros and that convert long numbers to scientific notation. Microsoft's example is exactly the case above: with the leading-zero option off, 00123 stays 00123 as text.
Two limits. The setting exists in Microsoft 365 and Excel 2024 only, not Excel 2021 or older. And Microsoft notes it does not affect data brought in through Power Query, which is the next method.
Excel: import through Data, From Text/CSV (any recent version)
This is the reliable route on every current Excel edition.
- On the Data tab, click From Text/CSV and choose the file.
- Click Transform Data to open the Query Editor.
- Click the header of the product code column, then Home, Transform, Data Type, Text. When Excel asks, choose Replace Current.
- Do the same for barcode or account number columns, then Close & Load.
The codes arrive as text with every zero in place. Set it up once and Excel remembers the query, so refreshing next month's file is one click.
Excel: typing or pasting codes
If you are entering codes by hand, format the cells as text first: select the column, press Ctrl+1, and on the Number tab choose Text. Then type. Anything typed before the format was applied has already lost its zeros.
A custom number format such as 00000 pads numbers to five digits on screen. That is display only. The stored value is still 123, a lookup against "00123" will still fail, and the padding is lost the moment the sheet is saved as CSV.
Google Sheets
Use File, Import rather than opening the file directly. Google's documentation covers the import location and separator choices in that dialog. The dialog also carries a conversion option, worded along the lines of converting text to numbers, dates and formulas, which Google's help page does not document; untick it before you import so the codes stay as text, and check the exact wording in your own copy of Sheets, since the dialog changes over time. If the codes are already in a sheet, select the column, open the Format, Number menu, and choose the plain text option before pasting new values in.
LibreOffice Calc
Calc shows a Text Import dialog when you open a CSV. In the preview at the bottom, click the product code column and set its type to Text. Calc's help describes the effect plainly: "The imported data is treated as text." The dialog also has a Detect special numbers option; with it off, Calc converts only plain decimal numbers and imports everything else as text.
How to tell if a file has already been damaged
- Codes in the same column have different lengths when the supplier's codes are all the same length.
- Long codes show as 1.23457E+12. Excel keeps only the first 15 digits of such numbers, so a 16-digit or longer code has been changed as well as reformatted.
- A code that should match a known product returns #N/A in a lookup.
If the damage is already in the only copy you have, ask the supplier for the file again. The zeros cannot be reconstructed with confidence.
When you compare two lists
The price list checker on this site treats codes as text and never strips zeros: 00123 and 123 are two different products, and the report says so. That is deliberate. If one of your files was opened the wrong way, the checker will show hundreds of "only in old list" and "only in new list" rows instead of matches, which is the honest answer and your cue to re-import the file.