Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leading Zeros
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leading Zeros
format - numbers - custom - 0000000000000 (13 zeros)
HTH "JSpence2003" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leading Zeros
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 copypaste especialvalue in column A Thanks, -- Farhad Hodjat "JSpence2003" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading Zeros | Excel Worksheet Functions | |||
Leading zeros | Excel Discussion (Misc queries) | |||
leading zeros | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) |