Page 1 of 1

[Solved] MB and GB Hidding zero's

Posted: Wed Apr 03, 2013 10:20 am
by 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.

Re: MB and GB Hidding zero's

Posted: Wed Apr 03, 2013 12:36 pm
by 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.

Re: MB and GB Hidding zero's

Posted: Wed Apr 03, 2013 12:47 pm
by Alabandit
Thanks... hope some one else has an alternative work around, but will use this as a fall back.

Re: MB and GB Hidding zero's

Posted: Wed Apr 03, 2013 12:48 pm
by Villeroy
Alabandit wrote: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.

Re: MB and GB Hidding zero's

Posted: Wed Apr 03, 2013 2:02 pm
by 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.

Re: MB and GB Hidding zero's

Posted: Wed Apr 03, 2013 2:13 pm
by 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.. 

Re: MB and GB Hidding zero's

Posted: Wed Apr 03, 2013 2:46 pm
by Alabandit
Thanks! Thant is exactly what I was looking for.

Re: MB and GB Hidding zero's

Posted: Wed Apr 03, 2013 2:47 pm
by Villeroy
gerard24 wrote: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.

Re: [Solved] MB and GB Hidding zero's

Posted: Fri Oct 04, 2019 5:33 pm
by CRISTIANOBC
To BR locale:

Code: Select all

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