Greetings,

Is it possible to create a file size custom number format? For example, a number would be formatted as KB, MB, etc.

A Bing search returns a number of suggestions, but the problem with the ones I've found is that the suggestion either changes the value, or turns the number into text. I would like to perform calculations on the value, so I just want to change the format.

Can I do this?

Thanks,

Hi cShane,

If I misunderstand your question, calculation(arithmetic operation) is this:
1 KB = 1024 x 1 Byte
1 MB = 1024 x 1 KB
1 GB = 1024 x 1 MB
Regards,

Re:  format number to include text

A custom number format similar to this should do want you want...
  #,##0.00" kb";-#,##0.00" kb";0;@

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
http://jmp.sh/K95N3ee

Thanks. This is actually explained well here:

http://en.wikipedia.org/wiki/File_size

This is a simple custom number format for B and KB (excluding MB, etc.):

[>1024]#" KB";#" B"

The problem is that when the number is greater than 1024, it displays as-is (i.e. 1025 KB), whereas it should be divided by 1014 (i.e. 1 KB). Can I change the number format, without changing the value?

Thanks,

OK, I've figured out this custom number format:

[>1000000]#,," MB";[>1000]#," KB";#" B"

There are two problems:

First, there seems to be an undocumented restriction that limits me to two conditions (in square brackets).

Second, the numbers are simply truncated (by the comma) and not divided by 1024, so the result is not accurate.

I have MB and KB as my two conditions, with bytes as the default. I can't add GB, which I really need.

It looks like custom number formatting is a dead end.

Is anyone familiar with conditional formatting? I've found a blog post for Excel 2007, but not 2010.

Thanks,

Hi Shane,

This forum is for developers discussing developing issues involve Excel like Excel UI customization and automation.

Since the issue is more relative to end-user, I would like to move it to Excel IT pro forum.

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thanks for your understanding.

Regards & Fei

This forum is for developers discussing developing issues involve Excel like Excel UI customization and automation.

I think that the only way to create a custom number format is to use VBA, which is why I posted in the developer forum.

Shane.

Hi Shane,

Please try this:

[<1000000]0.00," KB";[<1000000000]0.00,," MB";0.00,,," GB"

http://excelzoom.com/how-to-format-numbers-as-file-sizes/

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Regards,

George Zhao
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please click "tnfsl@microsoft.com"