
Jerrold Green1 Nov 7, 2013 12:20 PM
in response to helimedicNumbers 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 Nov 7, 2013 12:22 PM
in response to helimedicif you are not making calculations on those fields, have you considered formatting them as text? that would preserve the zeroes

Wayne Contello Nov 7, 2013 12:26 PM
in response to helimedic Solvedyou can also type a single quote first. like this:
'0000
'0001
'0002

Jiri Krecek Nov 7, 2013 12:39 PM
in response to helimedicIf 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
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 Nov 7, 2013 12:38 PM
in response to helimedicThank 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 Nov 7, 2013 7:23 PM
in response to Jerrold Green1Thanks, 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 Nov 8, 2013 12:05 AM
in response to Jiri Krecek@ 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 Nov 8, 2013 8:34 AM
in response to SGIIISGIII 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 Nov 8, 2013 9:05 AM
in response to Jerrold Green1I 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 Nov 8, 2013 9:41 AM
in response to SGIIIThe 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 Nov 8, 2013 9:53 AM
in response to Jerrold Green1Yes 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 columncondition 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 Nov 8, 2013 10:45 AM
in response to SGIIISGIII 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 columncondition 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 Nov 8, 2013 11:08 AM
in response to Jerrold Green1Yes, 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 Nov 8, 2013 11:41 AM
in response to SGIIISG,
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