Stop Excel removing leading zeros from barcodes

Excel removes leading zeros because it treats a barcode number as an amount, and in an amount a leading zero has no value. The fix is to tell Excel the column contains text, not numbers, before you type, paste or import the values. If the zeros have already gone, the safest repair is to re-enter the values from the original source.

Why Excel changes barcode numbers

When you type a string of digits into a cell with the default General format, Excel decides it is a number. That causes three separate problems for barcodes, product codes and other identifiers.

Leading zeros are dropped

A UPC-A code such as 012345678905 becomes 12345678905. To Excel the two are the same quantity. To a barcode scanner they are different codes, and the shorter one is no longer a valid UPC-A.

Long numbers appear in scientific notation

Numbers of 12 digits or more are often displayed in scientific notation, a shorthand for very large numbers. An EAN-13 code such as 8901234567890 may appear as 8.90123E+12, meaning 8.90123 × 10¹². In an Excel workbook the full number is usually still stored in the cell and only the display is shortened. The danger comes when the file is saved as CSV, or copied and pasted elsewhere, because the shortened version may be what gets saved.

Only 15 digits are kept

Excel stores numbers to a precision of 15 significant digits. Any digits after the fifteenth are replaced with zeros, and this change is permanent. EAN-13 and UPC-A codes fit within this limit, but longer identifiers such as 16-digit or 18-digit codes are corrupted as soon as Excel treats them as numbers.

How to keep the full digits in Excel

Menu names vary slightly between Excel versions and between Windows and Mac, but the options below exist in current versions.

Format the column as Text first

This is the most reliable method for new data.

Select the column that will hold the barcode values, by clicking its letter.
Right-click and choose Format Cells, or press Ctrl+1 (Cmd+1 on a Mac).
On the Number tab, choose Text and click OK.
Now type or paste the values.

Formatting must happen before the values are entered. Changing an existing column to Text afterwards does not bring back zeros that have already been removed. When pasting, use Paste Special → Values or Paste as Text where available, so the source formatting does not override the column.

Type an apostrophe first

For a few values, type an apostrophe before the number, for example ’012345678905. Excel stores the entry as text and does not display the apostrophe in the cell. This is practical for occasional edits, not for long lists.

Import CSV files instead of opening them

Double-clicking a CSV file opens it directly, and Excel converts the values as it opens them. Importing gives you control over each column.

Open a blank workbook.
Go to Data → From Text/CSV (in some versions, Data → Get External Data → From Text).
Select your file.
In the preview, choose to transform or edit the data, select the barcode column, and set its data type to Text. In the older import wizard, select the column in the final step and choose Text as the column data format.
Load the data into the sheet.

Excel for Microsoft 365 and Excel 2024 also have Automatic Data Conversion settings (Windows: File → Options → Data; Mac: Excel → Preferences → Edit) that can stop leading zeros being removed and long numbers being converted to scientific notation. Older versions do not have them.

Google Sheets

Google Sheets behaves in a similar way. To keep the digits:

Select the column.
Go to Format → Number → Plain text.
Type or paste the values.

When importing a CSV with File → Import, untick the option to convert text to numbers, dates and formulas, if it is shown.

If the zeros are already lost

First, find the original source of the numbers: the GS1 membership portal, your supplier’s product list, the packaging itself, or the system that issued the codes. Re-entering from the source is the only way to be certain the values are correct.

Padding the values back to a fixed length with zeros, for example with a custom number format or a formula such as =TEXT(A2,"000000000000"), only works when every value in the column should have exactly the same number of digits. If your list mixes UPC-A (12 digits) and EAN-13 (13 digits), or internal codes of different lengths, padding will produce wrong codes.

Numbers longer than 15 digits that were stored as numbers cannot be repaired in the spreadsheet at all. The final digits have been replaced and must be re-entered.

European CSV files use semicolons

In many European countries, the comma is used as the decimal separator, so Excel saves CSV files with semicolons between columns instead of commas. Files from suppliers in other regions may also use tab characters. This is normal, but it can make a file look like one long column when opened with different regional settings. Importing through Data → From Text/CSV lets you choose the correct delimiter.

How the Bulk Barcode Generator helps

Our Bulk Barcode Generator reads Excel .xlsx and CSV files and checks every row before you download anything. It is designed with these spreadsheet problems in mind:

It reads the full stored value from Excel cells that only display as something like 8.90123E+12.
It blocks rows where Excel has stored a number longer than 15 digits, because Excel has already changed it.
It blocks scientific-notation values in CSV files, because the full digits have already been lost.
It flags numbers that look as though they lost leading zeros.
For UPC and EAN values where a leading zero has clearly been removed, it blocks the row and shows the likely full number, so you can confirm against your source.
It reads comma, semicolon and tab-separated CSV files.

The file is processed in your browser and is not uploaded. You can work with up to 1,000 rows and files up to 5 MB.

Generate your barcodes

Once your column is stored as text, upload it to the Bulk Barcode Generator and review the row-by-row checks. If you are still deciding between formats, see EAN-13 vs UPC-A.