Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel: Unit Conversion (MB, GB, KB etc.)

Tags:

excel

I have a value a number of cells in a spreadsheet, all which cointain data which is a number followed by the Units

E.g

1.13 GB
134.3 MB
104.34 MB

What I am after to try and do is to standardize all of those to GB.

so:

1.13 GB -> 1.13 
    134.3 MB -> 0.1343
    104.34 MB -> 0.10434

etc.

I have found plenty of methods doing it in reverse, but none this way.

Virtual beer on the line for the winning formula :-)

like image 336
KingJohnno Avatar asked Jul 14 '15 13:07

KingJohnno


People also ask

How do I convert KB to MB in Excel?

If you want to convert mb to kb, just apply this formula: =A2*1024. Unit Conversion: (Convert between multiple units, such as time, distance, bits & bytes, etc. ) With Kutools for Excel's Unit Conversion utility, you can quickly convert between multiple units without any formulas.

How do you convert MB to GB in Excel?

Similarly, to convert it to GB, you need to divide it by 1024 * 1024 * 1024. What is this? Conversely, if you want to convert back, you need to multiply the storage size by 1024 each time. This means, to convert from MB to Bytes, you need to multiply by 1024 * 1024.


3 Answers

Here's another method:

  • Assumes the value in A1 is valid
  • Works from PB to KB (or nothing) and is easily extensible if necessary
  • As written normalizes to GB, but that is easily changed.
  • Assumes the UNITS are the last two characters of the string, if present

 =LEFT(A1,LEN(A1)-2)/10^((IFERROR(MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0),6)-3)*3)
like image 197
Ron Rosenfeld Avatar answered Oct 23 '22 18:10

Ron Rosenfeld


In B1 enter:

=IF(RIGHT(A1,2)="GB",--MID(A1,1,FIND(" ",A1)-1),--MID(A1,1,FIND(" ",A1)-1)/1000)

and copy down:

enter image description here

EDIT#1:

To handle GB. MB, KB, B, and no suffix, use this formula:

=IF(RIGHT(A1,2)="GB",--MID(A1,1,FIND(" ",A1)-1),IF(RIGHT(A1,2)="MB",--MID(A1,1,FIND(" ",A1)-1)/1000,IF(RIGHT(A1,2)="KB",--MID(A1,1,FIND(" ",A1)-1)/1000000,IF(RIGHT(A1,1)="B",--MID(A1,1,FIND(" ",A1)-1)/1000000000,A1/1000000000))))

enter image description here

like image 45
Gary's Student Avatar answered Oct 23 '22 17:10

Gary's Student


What you can do is to build two tables:

1. Building the legend table example place in spreadsheet: (=E1:F3) This one is intended to put in place the unit measures:

unit    in GB
 GB       1
 MB   =1/1024
 KB   =1/1048576

Meaning that 1 GB = 1 GB ; 1MB = 1/1024 GBand 1KB = 1/1048576 GB

Like so, everything in the table is standardized in GBs

2. Building the working table example place in spreadsheet: (=A1:C3)

unit    size               size in GB
 GB     1.13      =VLOOKUP(A1,$E$1:$F$3,2,FALSE)*B1
 MB     134.3     =VLOOKUP(A1,$E$1:$F$3,2,FALSE)*B2
 KB     104.34    =VLOOKUP(A1,$E$1:$F$3,2,FALSE)*B3

Like so, you can drag the size in GB formula and should there be anything to fix, you do that in the legend table and adjust it accordingly in the formula once.

Here's the visual: enter image description here

Hope this helps and I get to chill with a virtual beer.

like image 40
Newskooler Avatar answered Oct 23 '22 18:10

Newskooler