Previous 1 2 Next 16 Replies Latest reply: Nov 8, 2013 4:46 PM by SGIII
helimedic Level 1 Level 1 (0 points)

I am trying to input numbers such as 001, 002 etc. but the 00's in front keep getting dropped. I understand putting them there does not make this true or real numbers but I need them in this table. How do I get this without the 00's being dropped?


Numbers
  • Jerrold Green1 Level 7 Level 7 (29,950 points)

    Numbers knows that the leading zeros aren't necessary for expressing a Number value, so it drops them. There is another format which allows you to keep them, and it might work for you if the total number of places is always the same. Choose Numeral System from the Format > Cell menu, choose base ten and set the number of Places to 3, if that's what you need.

     

    Jerry

  • Jiri Krecek Level 4 Level 4 (1,070 points)

    if you are not making calculations on those fields, have you considered formatting them as text? that would preserve the zeroes

  • Wayne Contello Level 6 Level 6 (16,160 points)

    you can also type a single quote first.  like this:

     

    '0000

    '0001

    '0002

  • Jiri Krecek Level 4 Level 4 (1,070 points)

    If have a preset length of the field where the whole column must have the same length and you want to just backfill the leading zeroes, you could use a formula

     

    If you enter this in cell C2 and paste it down in C column, it will add leading zeroes to your numbers listed in column B

     

    =LEFT("00000",5−LEN(B2−1))&B2
    Screen Shot 2013-11-07 at 2.36.15 PM.png

     

    If you want to expand this to a larger length, just add as many zeros as you want in the formula "0000000000" and change the 5 to however many zeroes you have between the "" in the formula.

     

     

    If you want to add a constant number of zeroes (2 in my case) in front of the number, you could modify the formula to something like this:

     

    =LEFT("00000",2))&B2

     

    If you make it a formula, this would automatically turn the field format to show the leading zeroes, BUT..... you could still perform calculations on that field, which is a bonus!!!

  • helimedic Level 1 Level 1 (0 points)

    Thank you everyone for the suggestions. The single quote first was the easiest solution to my issue but I will keep the others in mind if I have a more complex table. Thanks again

  • SGIII Level 5 Level 5 (6,830 points)

    Thanks, Jerry, for that great tip on Numeral System Data Format.

     

    It displays the number as desired but keeps it a number, on which calculations can be made.  And no need to type those leading zeros.

     

    SG

  • Barry Level 7 Level 7 (29,470 points)

    @ Jiri

     

    A simpler version of the formula you presented would be this one (for  digits):

     

    =RIGHT("00000"&B2,5)

     

    As in your example, changing the number of places requires two edits to the formula:

     

    Incude as many zeroes between the quotes as you want places.

    Set the number of characters to be returned to match the number of places to be shown.

     

    9 places: =RIGHT("000000000"&B2,9)

     

    To append a fixed number of leading zeroes to any number, there's no need to use LEFT or RIGHT. Just use a string of as many zeroes as you want to append, and the concatenation operator:

     

    ="00"&B2

     

    In all cases, the number to be modified is in B2.

     

    Regards,

    Barry

  • Jerrold Green1 Level 7 Level 7 (29,950 points)

    SGIII wrote:

     

    Thanks, Jerry, for that great tip on Numeral System Data Format.

     

    It displays the number as desired but keeps it a number, on which calculations can be made.  And no need to type those leading zeros.

     

    SG

    SG,

     

    You're welcome. I learned it just the way you did, by reading the posts here, a few years back.

     

    I think you will find that most (but not all) numeric values, when formatted as text, will still work in the majority of functions and operators.

     

    Jerry

  • SGIII Level 5 Level 5 (6,830 points)

    I think you will find that most (but not all) numeric values, when formatted as text, will still work in the majority of functions and operators.

     

    When I try to sum a column of values entered with the leading ' that forces them to text, the result is 0. Same with the formula approaches above. I see I can reformat them as Number and they'll sum again. But your approach using the Numeral System format is the only one (I think) that leaves them as numbers that can be summed yet still display with leading zeros. That's pretty neat.

     

    On the subject of numbers as strings, I was wondering whether you or Barry know a reasonably simple way to automate the =RIGHT("00000000"&B2,9) to adjust to the largest number in a column so that there is at least one leading zero before every value in the column. That would be useful for that ugly multicolumn sort workaround I've posted elsewhere. I've been eyeballing, putting in a bunch of zeros and picking a number for the second argument, then adjusting and readjusting until I've got enough to pad the largest number in the column. Is there a better way?

     

    SG

  • Jerrold Green1 Level 7 Level 7 (29,950 points)

    The SUM function and some others that operate on a Range Argument are taught to ignore strings and to just grab the numeric formats. The normal arithmetic operators, and I think some single cell arguments in functions will work.

     

    Jerry

  • SGIII Level 5 Level 5 (6,830 points)

    Yes I've noticed that in, say, SUMIFS, when you want to find greater than or less than, those operators can be applied in the condition part of a column-condition pair to a "numeric string" or whatever that is called. But strings in the column part don't sum.

     

    Any ideas on automating =RIGHT("00000000"&B2,9) ?  MAX? LEN?  Can't piece it together.

     

    SG

  • Jerrold Green1 Level 7 Level 7 (29,950 points)

    SGIII wrote:

     

    Yes I've noticed that in, say, SUMIFS, when you want to find greater than or less than, those operators can be applied in the condition part of a column-condition pair to a "numeric string" or whatever that is called. But strings in the column part don't sum.

     

    Any ideas on automating =RIGHT("00000000"&B2,9) ?  MAX? LEN?  Can't piece it together.

     

    SG

    I would expect SUMIFS to behave similarly to SUM with respect to strings.

     

    Sorry, I don't understand what you mean by automating.

     

    Jerry

  • SGIII Level 5 Level 5 (6,830 points)

    Yes, as you say, it doesn't include numbers formated as strings in the SUM.

     

    By automating I mean that now I stuff a bunch of zeros into the formula, then take a stab at the second argument (9 in Barry's example) and hope for the best, increasing the number of zeros or the 9 as needed when I see the padding isn't enough. I was thinking, since you guys can make spreadsheets do incredible things, there must be a way for the formula to look at the column, find the biggest number in it, and adjust the padding accordingly for all the numbers in the column.

     

    SG

  • Jerrold Green1 Level 7 Level 7 (29,950 points)

    SG,

     

    The pad string of zeros doesn't have to be exact, just sufficient. You could always use a a string of a dozen zeros and not worry about it.

     

    Jerry

Previous 1 2 Next