Home |
Search |
Today's Posts |
#1
|
|||
|
|||
leading zeros in text format
I receive bulk orders via an Excel spreadsheet, containing many individual
orders with reference numbers. The reference numbers may be 7-9 digits long, however the field needs to have 10 digits. The format is set to customize: 0000000000. In this manner, we always see the 10 digits regardless of the actual number. The problem we have is that we bulk-load this spreadsheet through a specific custome built bulk-loader. This reference number field must have 10 actual digits, not format digits. Therefore a number such as 00865243 becomes 865243 and then will not run through the bulk loader. A work around is to format the column as text and then insert the leading zeros manually. This is ok, when the order is between 5 and 20, however we sometimes get 1500-2000 orders at a time. Manually adding the zeros will not work. I have tried converting into comma deliminated, and it still dropped the leading zeros. Any suggestions you have will be greatly appreciated. |
#2
|
|||
|
|||
Convert each reference number using:
=TEXT(A1,"0000000000") then copy the formula column and Edit Paste Special Value over the original. HTH Jason Atlanta, GA -----Original Message----- I receive bulk orders via an Excel spreadsheet, containing many individual orders with reference numbers. The reference numbers may be 7-9 digits long, however the field needs to have 10 digits. The format is set to customize: 0000000000. In this manner, we always see the 10 digits regardless of the actual number. The problem we have is that we bulk-load this spreadsheet through a specific custome built bulk-loader. This reference number field must have 10 actual digits, not format digits. Therefore a number such as 00865243 becomes 865243 and then will not run through the bulk loader. A work around is to format the column as text and then insert the leading zeros manually. This is ok, when the order is between 5 and 20, however we sometimes get 1500-2000 orders at a time. Manually adding the zeros will not work. I have tried converting into comma deliminated, and it still dropped the leading zeros. Any suggestions you have will be greatly appreciated. . |
#3
|
|||
|
|||
OK, one more try. I've been fat-fingering my keyboard.
Try this formula: =REPT("0",10-LEN(B1))&B1 .... where B1 is the reference number. |
#4
|
|||
|
|||
Thanks Jason and Dave, the =TEXT(A1,"0000000000") worked. I appreciate your
efforts. This will help ease a few OT hours off of our payroll. "Jason Morin" wrote: Convert each reference number using: =TEXT(A1,"0000000000") then copy the formula column and Edit Paste Special Value over the original. HTH Jason Atlanta, GA -----Original Message----- I receive bulk orders via an Excel spreadsheet, containing many individual orders with reference numbers. The reference numbers may be 7-9 digits long, however the field needs to have 10 digits. The format is set to customize: 0000000000. In this manner, we always see the 10 digits regardless of the actual number. The problem we have is that we bulk-load this spreadsheet through a specific custome built bulk-loader. This reference number field must have 10 actual digits, not format digits. Therefore a number such as 00865243 becomes 865243 and then will not run through the bulk loader. A work around is to format the column as text and then insert the leading zeros manually. This is ok, when the order is between 5 and 20, however we sometimes get 1500-2000 orders at a time. Manually adding the zeros will not work. I have tried converting into comma deliminated, and it still dropped the leading zeros. Any suggestions you have will be greatly appreciated. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
leading zeros in text format
I have a similar issue. My field is a three digit number and then text. It
is concatonated from twso other fields. ie: =+U15&" "&V15. The source field for the number is U15 and it has three digits, but when it pulls into the new field the leading zeros are dropped. How do I format this formula so that I can retain the three digit codes? Thanks! "Jason Morin" wrote: Convert each reference number using: =TEXT(A1,"0000000000") then copy the formula column and Edit Paste Special Value over the original. HTH Jason Atlanta, GA -----Original Message----- I receive bulk orders via an Excel spreadsheet, containing many individual orders with reference numbers. The reference numbers may be 7-9 digits long, however the field needs to have 10 digits. The format is set to customize: 0000000000. In this manner, we always see the 10 digits regardless of the actual number. The problem we have is that we bulk-load this spreadsheet through a specific custome built bulk-loader. This reference number field must have 10 actual digits, not format digits. Therefore a number such as 00865243 becomes 865243 and then will not run through the bulk loader. A work around is to format the column as text and then insert the leading zeros manually. This is ok, when the order is between 5 and 20, however we sometimes get 1500-2000 orders at a time. Manually adding the zeros will not work. I have tried converting into comma deliminated, and it still dropped the leading zeros. Any suggestions you have will be greatly appreciated. . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
leading zeros in text format
Maybe...
=text(u15,"000")&" "&text(v15,"000") Kristen Dahlmann wrote: I have a similar issue. My field is a three digit number and then text. It is concatonated from twso other fields. ie: =+U15&" "&V15. The source field for the number is U15 and it has three digits, but when it pulls into the new field the leading zeros are dropped. How do I format this formula so that I can retain the three digit codes? Thanks! "Jason Morin" wrote: Convert each reference number using: =TEXT(A1,"0000000000") then copy the formula column and Edit Paste Special Value over the original. HTH Jason Atlanta, GA -----Original Message----- I receive bulk orders via an Excel spreadsheet, containing many individual orders with reference numbers. The reference numbers may be 7-9 digits long, however the field needs to have 10 digits. The format is set to customize: 0000000000. In this manner, we always see the 10 digits regardless of the actual number. The problem we have is that we bulk-load this spreadsheet through a specific custome built bulk-loader. This reference number field must have 10 actual digits, not format digits. Therefore a number such as 00865243 becomes 865243 and then will not run through the bulk loader. A work around is to format the column as text and then insert the leading zeros manually. This is ok, when the order is between 5 and 20, however we sometimes get 1500-2000 orders at a time. Manually adding the zeros will not work. I have tried converting into comma deliminated, and it still dropped the leading zeros. Any suggestions you have will be greatly appreciated. . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format a cell to keep leading zeros. | New Users to Excel | |||
Adding Leading Zeros to Text | Excel Discussion (Misc queries) | |||
Leading Zeros in Numeric Values | Excel Worksheet Functions | |||
How do i change numbers in text format to number format? | New Users to Excel | |||
numbers and text in Excel to read as text keeping the leading zer. | Excel Discussion (Misc queries) |