MB and GB Hidding zero's

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


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:

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


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.

Got the answer here:

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

[>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)

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