[Solved] MB and GB Hidding zero's

[Alabandit]

I'm working on a table that has a large number of MegaByte, GigaByte and TeriByte Values. For the simplicity of math I assume every thing less than 1 is a KiloByte and all cells are MegaByte (Base 1000 not 1024).

Currently cell are formatted to have a tailing MB

Example:

Code: Select all

Photos    | Documents     | Videos
----------+---------------+------------------    
53.768 MB | 13 589.378 MB | 27 879 321.558 MB

Is there an easy way to format cells to hide the last 3 zero's but maintain the full value, so people reading the table would see:

Code: Select all

Photos    | Documents     | Videos
----------+---------------+----------    
53.768 MB | 13.589 GB     | 27.879 TB

or:

Code: Select all

Photos    | Documents | Videos
----------+-----------+----------    
53 MB     | 13 GB     | 27 TB

This would make it a lot easier to read, but cannot compromise the underlining maths. I would still need to be able to multiple 27TB by 2 and get 55758643,116 not 54.

Thanks for your help.

[Villeroy]

Hit Ctrl+F8 (menu:View>Highlight).
Constant numbers appear in blue font. Constant text appears in black font.

-- There is no way to apply number formats to text.
-- There is no way to apply a number format to a number so the cell displays a value other than the actual cell value. A numeric cell value can be displayed in many ways though.
-- A kilobyte has 1024 (2^10) bytes. 1000 (10^3) is a rough approximation. Same with mega, giga, tera.

IMHO, there is only one way to handle this mess: Enter/import/paste plain numbers and their units seperated from each other.
Having some numeric value in A1 and a unit text in B1, the formula to get the Bytes is:

Code: Select all

=A1*(2^(10*(MATCH(B1;{"b";"k";"m";"g";"t"};0)-1)))

assuming the units are B for single Bytes and K, M, G, T for kilo, mega, giga, tera.
(MATCH(B1;{"b";"k";"m";"g";"t"};0)-1) returns 0,1,2,3 or 4 depending on the position where B1 is matched in the {array} or error #N/A if B1 is something completely different.
Each unit is 2^10 times the previous one and this multiplied by the number in A1 gives the result in the smallest common unit Bytes which can be calculated with other Byte values and finally divided by 2^10, 2^20, 2^30 or 2^40 in order to get kilo, mega, giga and tera.

[Alabandit]

Thanks... hope some one else has an alternative work around, but will use this as a fall back.

[Villeroy]

Thanks... hope some one else has an alternative work around, but will use this as a fall back.

This is not a work-around. A work-around would try to calculate with your messy text data and that would be by far more complicated.

[Alabandit]

Not trying to offend but it's not text data, it's a number formatted in the same way as a currency to show a MB or GB at the end.

I simple don't want to go through dozens of sheets and add Columns from MB or GB, then re-layout 1000's of cells to make boarders smooth for printing. Though I admit that seems like the only solution.

[gerard24]

If your data are numerics and if they are entered with the same unity (MB), Format > Cell, define this format :

Code: Select all

[>1000000]0,000  " TB";[>1000]0,000 " GB";0,000" MB"

Note there is one space between 0.000 and " GB" and 2 spaces between 0.000 and " TB". (of course no space for MB)

Edit: Sorry, my answer is for French locale where space is the thousand separator. With English locale and comma as thousand separator, replace space with comma : Code: Select all [>1000000]0.000,," TB";[>1000]0.000," GB";0.000" MB" For other locale, use the thousand separator for this locale (dot for german???) etc..

[Alabandit]

Thanks! Thant is exactly what I was looking for.

[Villeroy]

If your data are numerics and if they are entered with the same unity (MB), ...

Well, if they are entered at all. Unfortunately, we never get to know how data get into some sheet and if they are numbers or text.
From this forum's experience I would guess that we are speculating on text imported or pasted from some human readable text, html, pdf, anything like that.
Rubbish in, rubbish out.

[CRISTIANOBC]

To BR locale:

Code: Select all

[>999999,99]0,000.. " TB";[>999,99]0,000. " GB";0,000" MB"