Help with Numbers. I am trying to input numbers such as 001, 002 etc.

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

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

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

'0000

'0001

'0002

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

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

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

@ 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

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

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

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

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

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

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

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

Sure, I could put a dozen zeros and 10 or something as the second argument. Overpadding isn't elegant, but it works. Just thought you or Barry might know how to have it adjust to the length of the largest number in the column.

SG