Archive
This post is archived and may contain outdated information. It has been set to 'noindex' and should stop showing up in search results.
Excel: How to Format UPC With Leading Zero
Jul 17, 2012WindowsComments (1)
ExcelHaving trouble getting UPC numbers to show up properly in Microsoft Excel? Excel may format a UPC strangely if the cell is formatted as Text (e.g. 8.08282E+11) or may remove leading 0's if the cell is formatted as a Number. Some solutions involve using a function or adding a single-quote to the beginning of each UPC, but those don't work well with bulk UPCs. The better solution is to use a Custom format for the UPC cells.

Since UPCs have 12 digits, you can use a Custom format to make Excel format the UPC as a 12-digit number and fill in the leading 0. To do so, right-click the UPC cell (or range of cells), click "Format Cells...", select "Custom", and then enter twelve 0's into the "Type" field like so:



What this does is format the UPC as a number and then fill in leading 0's if your input is less than 12 digits. Excel still removes leading 0's from input, but this makes sure the leading 0 still displays, and will be present if you export to CSV or TXT.

Comments (1)
Add a Comment
Gabriel Moreiradasilva   Sep 18, 2018
You also may format the number as custom as a 0 followed by 11 #'s. 0###########