Dec 15, 2006

How To Convert UPC's Into 13-Digit

For those of us that have copied from ProSpace and pasted into Excel we're all too well familiar with Excel stripping off the zero's from the UPC's. That's because Excel looks at the data in that column and assumes it should be formatted as numerical. There are two ways to fix this.
  1. If you have just pasted the data into Excel, select the UPC column in Excel and change the format to text. Since the ProSpace data is still in clipboard you can now paste again. The UPC column should have retained the leading zero's on your UPC.
  2. If you're working with an existing Excel file you can use the following formula to make a copy of the UPC in your data and add enough zero's to make it 13 digits. This formula is based on UPC being in the first column. =rept(0,13-len(A2))&A2 If this were to be converted to English it would read "repeat the number "0" 13 times minus the number of characters in cell A2 then concatenate these zeros with the data in A2".

2 comments:

Anonymous said...

Another way to accomplish this without creating another column is to select Format, Cell, Custom and type in 13 zeros.

Joanne McClain
Kraft Foods

Geoff said...

We also use the =text(A1,"0000000000000") formula to give use this.

Geoff Rathmell
Anderson Merchandisers