Leading Zeros

[JSpence2003]

I have a list of 1300 UPC's that I need to bring into Excel. The original
list is in SQL. I know the tricks of formatting a cell as TEXT or Custom to
the # of characters in your data, but if/when I do that, and I go to SQL and
copy the data, then go to Excel and paste it in, the formatting does not
stay.

The UPC's are not uniform, in that each one contains 1 leading zero. Some
may have 2, 3, 4, etc leading zeros. But each is 13 characters long.

Any suggestions as to how to get Excel (I am running 2007) to keep these
zeros?

Seems like people have complained about this forever and Microsoft has never
fixed it.

 

[Brad]

format - numbers - custom - 0000000000000 (13 zeros)

HTH

 

[Farhad]

Hi,

As my underestanding you input some data from SQL to Excel and because of
the data was numbers you lost the Zeros in the left of your data so if i am
right then you can use a helper column and enter the formula below in it
assumed your data is in column A enter this formula in B1

=REPT("0",13-LEN(A1))&A1

and then copy drag down to where ever you need after that you can select all
data in column B and copy>paste especial>value in column A

Thanks,