Leading zeros removed from CSV file? Restore them here

Excel reads a cell made only of digits as a number when it opens a CSV, and a number has no leading zeros. Long numbers show in scientific notation, like 1.23E+15, and Excel keeps only 15 significant digits, so digits past the 15th become zeros.

A CSV stores no column types, so this happens every time the file is opened in Excel. Save the file from Excel and the shortened values are what gets saved.

Example: ZIP code 02134 opens as 2134. UPC 049634061230 opens as 49634061230.

Import the file instead of opening it. In Excel, go to Data > From Text/CSV, open the query editor, set those columns to Text, then choose Close & Load.

• Excel for Microsoft 365 and Excel 2024 have an Automatic Data Conversions setting that can stop Excel removing leading zeros.
• An apostrophe before a number, like ‘02134, keeps it as text.
• Square warns that spreadsheet editors can strip leading zeros from SKUs. Its import also takes .xlsx files.

The Excel file this tool gives you stores the fixed columns as text, so the zeros stay when you open it.

Put =TEXT(A2,”00000”) in an empty column, fill it down, then paste the results back as values. ZIP+4 codes are five digits, a hyphen and four digits, so a 9-digit value needs =TEXT(A2,”00000-0000”).

Example: 2134 becomes 02134. 21341234 becomes 02134-1234.

The tool does this for a whole column at once, including columns that mix 5-digit ZIPs and ZIP+4.

Only when every digit is still in the file. The number of digits is the digits before the decimal point plus the exponent.

Formula: digits = digits before the point + exponent.

Example: 8.5E+11 has 1 + 11 = 12 digits, and only 2 of them survive: 85xxxxxxxxxx. That row has to be re-keyed from the original export, the vendor’s list or the product label.

If the sheet is still open in Excel and unsaved, format the column as Number with 0 decimal places to see the full value. That works up to 15 digits.

The tool lists every such row with its item name, so you can re-key them in one pass.

It recomputes the GS1 check digit, the last digit of every UPC-A (12 digits), EAN-13 (13 digits) and GTIN-14. Multiply the other digits by 3 and 1 in turn, starting from the right, add them up, and subtract the sum from the next multiple of ten.

Example: for 04963406123, the weighted sum is 70, so the check digit is 0 and the full UPC is 049634061230.

A padded code that fails the check had a typo before Excel touched it. The UPC check digit calculator checks single codes, and our field guide on barcode inventory for a small warehouse covers the scanning side.