number format

[lezawang]

Hi
I went to format Cell-->custom. I see different formats like #,##0
what does that format mean? how you interpret that format?

also how can I know what kind of formatting applied on a cell? Lets say a cell formatted as %, how can I know this cell formatted as %.


Thank you

 

[Norie]

Have a look here for a guide to custom number formatting.

 

[Rick Rothstein]

Here is another place you can look also...

https://peltiertech.com/Excel/NumberFormats.html

 

[lezawang]

Thank you all for your reply and interesting links. I really appreciate that.
I have this question. Lets say I typed this code #,#

if i type 1000 excel will type 1,000

so this is my interpretation: since the code is #,# then if i type 1000 then excel will go from left to right.. will go from left and count
3 digits and then put , after these 3 digits then what is remain which is 1 put it after the "," therefore excel will type 1,000

now if i type 1000000 why excel type 1,000,000 ? i thought excel will say since the code is #,# then i will start from the right to left
and count three digits and then put "," and what left i put it after the "," therefore i thought excel will type 10000,000
but to my surprise excel typed 1,000,000
why is that? how you understand this code #,#

thanks a lot once again

 

[Rick Rothstein]

Thank you all for your reply and interesting links. I really appreciate that.
I have this question. Lets say I typed this code #,#

if i type 1000 excel will type 1,000

so this is my interpretation: since the code is #,# then if i type 1000 then excel will go from left to right.. will go from left and count
3 digits and then put , after these 3 digits then what is remain which is 1 put it after the "," therefore excel will type 1,000

now if i type 1000000 why excel type 1,000,000 ? i thought excel will say since the code is #,# then i will start from the right to left
and count three digits and then put "," and what left i put it after the "," therefore i thought excel will type 10000,000
but to my surprise excel typed 1,000,000
why is that? how you understand this code #,#

First off, if you type in #,# for the Custom Format type pattern, it does not remain as that, if you go back to the Custom Format for that same cell, you will see Excel has expanded it to #,###. The comma in that pattern tell Excel to format the number in the cell with thousands separators... thousands separators are placed by counting digits from right to left which is why you are seeing the commas where you do.

 

[lezawang]

Thank you for you help. I have another question if you do not mind
I have numbers like
10.123
222.1234
40.0303
1.4332

how can I use the custom to make number integer (no decimal) like
10
222
40
1

I know I can use "Increase" or "Decrease" decimal but I want to see how can I do that using custom code. Thanks

 

[Rick Rothstein]

Thank you for you help. I have another question if you do not mind
I have numbers like
10.123
222.1234
40.0303
1.4332

how can I use the custom to make number integer (no decimal) like
10
222
40
1

I know I can use "Increase" or "Decrease" decimal but I want to see how can I do that using custom code. Thanks

You can find out what the Custom Format type pattern is for any cell formatting you use from the Category list by applying that format to a cell (here is would be Number with 0 decimal points) then selecting that cell and going back into the Cell Formatting dialog box and picking Custom from the Category list... it will show you what the Custom Format type pattern is for the selected cell even if the original format was not done form the Custom category item.

 

[lezawang]

Thanks once again for the great help. I select this custom formatting #,##0 and by that I got rid of the decimal. But I do not understand the logic behind it. Why after applying that formatting to a number like 48.66666 the result would be 48 ? it is great, that is what I want but how can this format #, ##00 do the trick. The following is my understanding

48.66666

the 48 is the first # from the left hand side
then the ,
then ## are the first 2 number after the period which are 66
and the 00 should be added after these 2 numbers.

so the result should be 48.6600