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

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 2

I've always found that the easiest way is to use the raw byte value, then divide by 1024^n, depending on whether you want KB, MB or GB

Answer 3

I will gladly share a beer with Abe Gold. Or if you all could get me reputation where I can comment on Abe Gold's entry, that would be great too.

Here's the solution that worked for me, building from Abe's. It uses standard US formatting (changed semicolons to commas) and just returns the value of the cell being evaluated if (gb,mb,kb,b) are not found. (paste this text to A2, then copy to wherever you need it for further updates):

=IFERROR(VALUE(IFERROR(LEFT(A1,FIND(" ",A1)),A1))/IF(ISERROR(SEARCH("gb",A1)), IF(ISERROR(SEARCH("mb",A1)),IF(ISERROR(SEARCH("kb",A1)), 1073741824, 1048576), 1024), 1),A1)

If you have Office 2019/Office365, you can use this:

=IFERROR(VALUE(IFERROR(LEFT(A1,FIND(" ",A1)),A1))/IFS(ISERROR(SEARCH("gb",A1)),1,ISERROR(SEARCH("mb",A1)),1024,ISERROR(SEARCH("kb",A1)),1048576,ISERROR(SEARCH("b",A1)),1073741824),A1)

Answer 4

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 5

I liked Amit answer but with bytes instead of bits, and since I saw no answer in bytes I post this one. Also my locale uses ; instead of ,

=VALUE(IFERROR(LEFT(D2;FIND(" ";D2));D2)) /IF(ISERROR(SEARCH("gb";D2)); IF(ISERROR(SEARCH("mb";D2));IF(ISERROR(SEARCH("kb";D2)); 1073741824; 1048576); 1024); 1)

Answer 6

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.

Answer 7

It's pretty long, but assuming your value is at A1:

=VALUE(IFERROR(LEFT(A1,FIND(" ",A1)),A1)) /
  IF(ISERROR(SEARCH("gb",A1)), IF(ISERROR(SEARCH("mb",A1)),
  IF(ISERROR(SEARCH("kb",A1)), 1000000000, 1000000), 1000), 1)

This handles the cases of gb, mb, kb and nothing specified (bytes). It's not case sensitive (gb,gB,GB,Gb), and the only restriction is to have a space character after the value (or nothing in case of bytes)

Answer 8

In B2:

=IF(ISERROR(FIND("M",A1))=FALSE,VALUE(LEFT(A1,FIND(" ",A1)-1))/1000,IF(ISERROR(FIND("K",A1))=FALSE,VALUE(LEFT(A1,FIND(" ",A1)-1))/1000000,VALUE(LEFT(A1,FIND(" ",A1)-1))))