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

Question

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

Answer 1

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)

Answer 2

In B1 enter:

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

and copy down:

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

Answer 3

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:

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