How to Show Excel UPC Numbers in Full: Step-by-Step Guide

If you work with UPC (Universal Product Code) numbers in Excel, you may notice that long numbers sometimes appear in scientific notation (e.g., 1.23457E+12) or get truncated. This can be frustrating when you need to see the full UPC number, especially for inventory management, barcode labels, or product catalogs.

This guide explains how to display UPC numbers in full in Excel, without losing any digits, and offers tips to prevent formatting issues in the future.

Why UPC Numbers Don’t Display Correctly in Excel

Excel is designed primarily for numbers, not codes. When you enter a 12-digit UPC, Excel might:

Convert it to scientific notation (e.g., 123456789012 → 1.23457E+11)
Round off the last digits, losing accuracy
Remove leading zeros, which are crucial for UPC codes (e.g., 012345678905 → 12345678905)

To display UPC numbers correctly, you need to change the cell format.

Method 1: Format Cells as Text

This is the simplest and most reliable method.

Select the cells containing UPC numbers.
Right-click and choose Format Cells.
In the Number tab, select Text.
Click OK.
Re-enter your UPC numbers if they were already typed — Excel will now treat them as text and display all digits, including leading zeros.

✅ Tip: You can also type an apostrophe (') before entering the number (e.g., '012345678905) to force Excel to treat it as text.

Method 2: Use a Custom Number Format

If you prefer to keep the cell as a number, you can use a custom format:

Select the cells with UPCs.
Right-click → Format Cells → Number → Custom.
In the Type field, enter a string of zeros matching your UPC length (e.g., 000000000000 for 12-digit UPCs).
Click OK.

Now Excel will display all 12 digits, including any leading zeros, without switching to scientific notation.

Method 3: Importing UPC Numbers Correctly

When importing UPCs from a CSV or other source:

Open Excel.
Go to Data → Get External Data → From Text.
Select your file and choose Delimited or Fixed Width.
In the Column Data Format step, choose Text for columns with UPCs.

This ensures Excel doesn’t truncate digits during import.

Method 4: Use Formulas to Preserve Leading Zeros

If you already have numbers truncated or missing leading zeros, you can use the TEXT function:

Replace A1 with the cell containing your UPC.
This will format the number as a 12-digit string with leading zeros if needed.

Common Issues and How to Fix Them

Numbers still show in scientific notation: Make sure the cells are Text or use a custom number format.
Leading zeros disappear: Always use Text format or the TEXT function.
Copy-pasting breaks the format: Use Paste Special → Values to maintain formatting.

Best Practices for UPC Numbers in Excel

Always format UPC columns as Text before entering or importing data.
Avoid formulas that perform arithmetic on UPCs—they are identifiers, not numbers.
Consider creating a template spreadsheet with UPC columns pre-formatted to Text.
Use the TEXT function if you need to concatenate UPCs with other text.

Final Thoughts

Displaying UPC numbers in full in Excel is easy once you understand how Excel handles long numbers. Using Text formatting, custom number formats, or the TEXT function ensures all digits are preserved, including leading zeros. By following these best practices, you can manage inventory, barcodes, and product data accurately without losing information.