Forum Discussion
Building UPC in Excel - issue is keeping leading zeros and eliminating spaces between the numbers
am building UPC codes using data imported into Excel. When copying this data over to a new column to create the UPC codes, I need to keep the leading 0's but eliminate the spaces between the columns....
Your formula generated a #VALUE error for me. If ou have a text string,
0000 007 1600
then
= SUBSTITUTE(text, " ", "")
will remove the spaces and leave the remaining charactes, including leading zeros. If, on the other hand, Excel has already been converted the string to a number
71600
then converting it to text with a number format
= TEXT(number, REPT("0",11))
should work. I have no idea why formulas should be lost when you close a workbook.