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.

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.
.

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.

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.
.

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.
.

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