Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
BigBrook
 
Posts: n/a
Default 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   Report Post  
Jason Morin
 
Posts: n/a
Default

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   Report Post  
Dave O
 
Posts: n/a
Default

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   Report Post  
bigbrook
 
Posts: n/a
Default

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format a cell to keep leading zeros. Shadyhosta New Users to Excel 5 July 27th 05 04:37 PM
Adding Leading Zeros to Text Jenn Excel Discussion (Misc queries) 4 January 12th 05 07:51 PM
Leading Zeros in Numeric Values DBavirsha Excel Worksheet Functions 6 January 4th 05 06:21 PM
How do i change numbers in text format to number format? Greg New Users to Excel 1 December 14th 04 06:22 PM
numbers and text in Excel to read as text keeping the leading zer. Ralph Excel Discussion (Misc queries) 2 December 10th 04 08:05 PM


All times are GMT +1. The time now is 01:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"