Format To Show Specified Numbers

  • All,


    I have a number format that I need assistance with:


    The number format I need is 1234-000 or 1234-001


    The normal number format is 1234-000, very rarely does the 1234-001 be used. So I would like the number format to left fill the left section 1234 and right fill the right section 001.
    Is this possible?

  • Re: Left And/or Right Fill Number Formating


    That's the format I currently have. But if only 4 digits are used, it shows like 0001-234 instead of 1234-000. The reason I need this is, very rarely is the last 3 digits used most entries are only 4 numbers. And to alleviate any typo's where the user is used to only using 4 digits this could confuse a lot of people or mess up our data by adding 3 extra entries. As I stated the format I am looking for is to right fill the right portion of the number series and left fill the left portion of the number series.

  • Re: Left And/or Right Fill Number Formating


    I can't get what you really want, but based on;

    Quote

    So I would like the number format to left fill the left section 1234 and right fill the right section 001.


    1234-000

  • Re: Format To Show Specified Numbers


    Not sure if what you are asking is possible, but here are 2 options I came up with - kind of an ugly way to get there, but it seems to do what you are asking


    opt 1
    Try formatting the cells ####-"000"
    Excel treats the "000" as text appended to the number
    Only catch is you have to type the hyphen if the entry is all 7 digits


    opt 2
    Assuming data is in column A
    Format column B to ####-###
    In column B, set formula as =IF((LEN(A1)>4),A1,A1*1000)


    Hope this helps


    -Brandon

  • Re: Format To Show Specified Numbers


    Try this custom format: [COLOR="Blue"][>9999]0000-"00"0;[>=0]0000-"000";General[/COLOR]


    So,


    nnnn shows as nnnn-000
    nnnnm shows as nnnn-00m

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!