+ Reply to Thread
Results 1 to 4 of 4

leading zeros in text format

  1. #1
    BigBrook
    Guest

    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. #2
    Dave O
    Guest

    Re: leading zeros in text format

    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.


  3. #3
    Jason Morin
    Guest

    Re: leading zeros in text format

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


  4. #4
    bigbrook
    Guest

    Re: leading zeros in text format

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

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1