Easy 13 Digit UPC Fields in Excel Tip

Over the years I have seen and used several methods to format a UPC field in Excel with leading zeros, forcing it to use 13 characters. More recently, I have always used the =TEXT(A1,"0000000000000") formula next to a column of UPCs and then copy/paste special values over the original data.

However just the other day I read about the easiest formatting method I have yet to witness for formatting 13 digit UPC fields (or other similar fields) with leading zeros. I can't take credit for the following tip but it is well worth sharing and making sure everyone is aware of it.

1) Select the entire column of UPCs
2) CTRL-1 to bring up Format Cells dialog
3) Select "Custom" under Category on the Number tab. (Custom Number Format)
4) In the "Type:" field, change from General to 13 zeros: 0000000000000
5) Done


UPDATE 2/20/09
Well it appears that this is not the be-all-end-all solution that I was hoping for.
When you change the cell formatting as mentioned above, that is all you are doing, changing the format of how Excel displays the number with-in the cell. So if you try to use VLOOKUP or another similar reference function on these UPC numbers, it will fail to find the reference if one cell range is text and the other is a number, despite both ranges being formatted to appear the same.

So... I plan to still use my =text(A1,"0000000000000") copy/paste values as the primary method for saving 0 leading UPCs in a column.

Bottom line, if you just need your UPCs to be formatted as a leading zero cell, by all means, use the custom format trick. Just be aware that you won't be able to easy reference the cell value as a zero lead UPC...

Geoff