Excel: How to Format UPC With Leading Zero

Jul 17, 2012   Windows   Nick Vogt   Comments (1)
Please note that this post is over a year old and may contain outdated information.
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.

Share This Post

Comments (1)

Gabriel Moreiradasilva   Sep 18, 2018
You also may format the number as custom as a 0 followed by 11 #'s. 0###########
Share This Post
H3XED © Nick Vogt   RSS   Policies   Twitter