How to show "0" before a number that has a custom format?

[harrisonchu88]

Hi,

We are a small food manufacturing company and I have made an Excel spreadsheet to keep track of our assigned UPC codes for all our SKU's.

I utilize the Azalea add-on to calculate the UPC-A 12th digit check number, which I have displayed in a 2nd column. The 1st column consists of the first 11 digits, but I have the cells in a custom format "# ##### #####" so that we punch in the numbers in sequence and it fixes the displayed format.

However, I just ran into a problem where I have a OEM brand packaging out in Canada where their UPC code starts off with a "0" as the first digit. Excel automatically gets rid of the "0", thus screwing up the check digit calculation. If I enter " '0 ##### ##### ", the spaces for some reason change the check digit calculation! I have to enter " '0########## " for the correct check digit to come out, but this makes it harder to reference the UPC code when we need to do so.

It's not a big deal right now since we only have 3 SKU's that are affected, but for future reference I thought I should consult the forum to see if anyone has any thoughts on this? Is there any way to simply disable whatever Excel process that removes the 0? (I tried searching online but all the results I see say to use the ' prior to the 0, or to format the numbers as text).

Thank you!

[vcoolio]

Hello Harrison,

Leading zeros will always make a person scratch their head!

Maybe, just maybe, if you change your Custom Format from # ##### ##### to 0 00000 00000 (these are all zeros BTW), it may work.

So, a normal code number entry of say 25555566666 should look like this:-

2 55555 66666

and an entry of say 05555566666 should look like this :-

0 55555 66666

I haven't tested it, but its worth a try.

Cheers,
vcoolio.

[M E Good @ XL4M]

G'day Harrison

This might help
Format column A as Text
A1 enter an 11 digit code (no leading zero)
B1 =IF(LEN(A1)=12,TEXT(A1,"0# ##### #####"),TEXT(A1,"# ##### #####"))
Now try a 12 digit code (any 1 digit leading number including zero)

Hope this is of some use.

[harrisonchu88]

I had completely forgotten about this post (not receiving e-mails even though subscriptions are active and enabled in account settings). Just wanted to come back and advise, since my OP, GS1 has since made available both web calculator as well as an .xls sheet.

Unfortunately, I can't seem to post links yet with my account, but they are the first couple results when you search for "excel UPC check digit calculator" on Google search.

And thank you to the gentlemen who took the time to respond to my OP!