+ Reply to Thread
Results 1 to 5 of 5

hyphenated number format with leading zeros to the right of the hy

  1. #1
    CJ
    Guest

    hyphenated number format with leading zeros to the right of the hy

    I need to achieve a result like this: 12345-0001
    However, the user does not want to type the leading zeros.

    Help would be much appreciated!!!

  2. #2
    Gary''s Student
    Guest

    RE: hyphenated number format with leading zeros to the right of the hy

    From your example I assume the numbers are of the form:

    5 digits -000 1 digit

    if the user just types in 123451 and the next cell has the formula:
    =LEFT(A1,5)&"-000"&RIGHT(A1,1)
    then it will display 12345-0001
    just as you want.
    _____________________________________
    Gary's Student


    "CJ" wrote:

    > I need to achieve a result like this: 12345-0001
    > However, the user does not want to type the leading zeros.
    >
    > Help would be much appreciated!!!


  3. #3
    B. R.Ramachandran
    Guest

    RE: hyphenated number format with leading zeros to the right of the hy

    Hi,

    Enter the following formula in B1, assuming that A1 contains the
    user-entered number.

    =LEFT(A1,5)&"-"&RIGHT(10000+RIGHT(A1,LEN(A1)-5),4)

    The formula will convert,

    123451 to 12345-0001
    1234512 to 12345-0012
    12345123 to 12345-0123
    123451234 to 12345-1234

    If the user enters a number containing less than 6 digits, it would return
    an error as #VALUE!

    If you have several user-entered numbers in column A (eg., A1:A101) enter
    the above formula in B1 and fil-in the formula down to B101.

    Hope this helps,
    Regards,
    B. R. Ramachandran

    "CJ" wrote:

    > I need to achieve a result like this: 12345-0001
    > However, the user does not want to type the leading zeros.
    >
    > Help would be much appreciated!!!


  4. #4
    CJ
    Guest

    RE: hyphenated number format with leading zeros to the right of th

    Thank you so much and also for the fast response! I kind of figured I was
    going to have do something to the cell next to it but couldn't figure it out.

    "Gary''s Student" wrote:

    > From your example I assume the numbers are of the form:
    >
    > 5 digits -000 1 digit
    >
    > if the user just types in 123451 and the next cell has the formula:
    > =LEFT(A1,5)&"-000"&RIGHT(A1,1)
    > then it will display 12345-0001
    > just as you want.
    > _____________________________________
    > Gary's Student
    >
    >
    > "CJ" wrote:
    >
    > > I need to achieve a result like this: 12345-0001
    > > However, the user does not want to type the leading zeros.
    > >
    > > Help would be much appreciated!!!


  5. #5
    CJ
    Guest

    RE: hyphenated number format with leading zeros to the right of th

    Thank you!

    "B. R.Ramachandran" wrote:

    > Hi,
    >
    > Enter the following formula in B1, assuming that A1 contains the
    > user-entered number.
    >
    > =LEFT(A1,5)&"-"&RIGHT(10000+RIGHT(A1,LEN(A1)-5),4)
    >
    > The formula will convert,
    >
    > 123451 to 12345-0001
    > 1234512 to 12345-0012
    > 12345123 to 12345-0123
    > 123451234 to 12345-1234
    >
    > If the user enters a number containing less than 6 digits, it would return
    > an error as #VALUE!
    >
    > If you have several user-entered numbers in column A (eg., A1:A101) enter
    > the above formula in B1 and fil-in the formula down to B101.
    >
    > Hope this helps,
    > Regards,
    > B. R. Ramachandran
    >
    > "CJ" wrote:
    >
    > > I need to achieve a result like this: 12345-0001
    > > However, the user does not want to type the leading zeros.
    > >
    > > Help would be much 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