Field guides / 01

How to Stop Excel from Removing Leading Zeros in Barcode Numbers

A scanner must return the identifier in your inventory system, including its first zero. Treat identifiers as text even when every character is a digit.

First, diagnose what changed

• A missing zero: If 012345678905 becomes 12345678905, the text no longer matches your 12-digit product record. Do not send the shortened value to a generator that accepts an 11-digit UPC base: it can calculate a new check digit for a different number.
• An E in the display: A cell showing 4.00638E+12 may still contain all of 4006381333931. Inspect the formula bar and compare with the source. The display alone does not prove lost digits.
• A rounded serial: Numeric cells preserve at most 15 significant digits. If a longer serial was converted and digits changed, fetch the original text. A formatting change cannot recover it.

Method 1 · Set up the column before entry

1. In a new worksheet, select the identifier column and set its number format to Text.
2. Type a test identifier beginning with zero. For an external list, paste original plain text; copying an already converted numeric cell cannot restore digits.
3. Compare the cell content to the source before pasting the rest. Keep the original export as a separate file.

Method 2 · Restore a known fixed width

If the product master specifies a complete 12-digit UPC and only leading zeros were removed, enter =TEXT(A2,"000000000000") in B2. For 12345678905, the expected result is 012345678905. Fill down, then copy the results as text.

Use thirteen zeros for a known complete EAN-13 or eight for a fixed-width internal tag. Never guess the width of a mixed column. In particular, an 11-digit UPC base awaiting its check digit is not a damaged 12-digit UPC.

Method 3 · Import a CSV without guessing types

1. Open the original CSV in a text viewer and check a sample record. Opening a CSV does not itself rewrite the file; saving converted data can.
2. In desktop Excel choose Data → From Text/CSV → Transform Data. Look for an automatically inserted Changed Type step. Remove or edit the step that converted the identifier to a number.
3. Set the original column to Text before numeric conversion. Check the preview, then choose Close & Load. Menu names vary by Excel version.

Before generating a batch

Check =LEN(B2) against the expected width. For a known source string in C2, use =EXACT(B2,C2). Inspect unintended spaces: TRIM also collapses repeated internal spaces, so apply it only if your naming rules permit that change.

Reference: Microsoft · Keeping leading zeros and large numbers