leading zeros in text format

[Guest]

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.

 

[Jason Morin]

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

 

[Dave O]

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.

 

[Guest]

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.

 

[Kristen Dahlmann]

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!

 

[Dave Peterson]

Maybe...

=text(u15,"000")&" "&text(v15,"000")

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!