Forum Discussion
Adding and removing leading zeros from UPCs
Hi there - I'm working with multiple programs that access our barcodes for multiple purposes. Some require the leading zeros of a 14-digit UPC and others require that there are no leading zeros. None of the solutions for cell formatting seem to work - I've tried the custom formatting and it doesn't seem to work, so not sure if I'm doing something wrong. Thanks for any input!
- As SnowMan55 replies you may change the _look_ of values by using ctrl+1, custom, type.
If you need to change the _value_ you will never have leading zeros on numbers. You must then change to strings/text. The value is normally necessary when you communicate with other programs.
It is probably best to always have the codes saved as text even though they may be converted to numbers when inserted into Excel (that defaults to make data possible for calculations).
=TEXT(A2, "000000000000000") or =TEXT(A2, REPT("0";15)) calculates the number in A2 to a fifteen figures string.
=TEXT(A2, "0") calculates the number in A2 to a string as short as possible but never shorter than one figure. See the attached workbook.
