Geek Answers Handbook

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

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

eg

1.13 GB 134.3 MB 104.34 MB

What I'm trying to do is standardize all of them in GB.

So:

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

and etc.

I found many methods doing this in the reverse order, but not like that.

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

+7
source share
7 answers

In B1, enter:

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

and copy:

enter image description here

+3
source

Here is another method:

  • The value in A1 is assumed to be valid.
  • Works from PB to KB (or nothing) and can be easily expanded if necessary
  • As written, it normalizes to GB, but it's easy to change.
  • UNITS are assumed to be the last two characters of the string, if any
  =LEFT(A1,LEN(A1)-2)/10^((IFERROR(MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0),6)-3)*3)
+10
source

What you can do is build two tables:

1. Creating a table of tables An example in a table: ( =E1:F3 ) This is intended for setting units:

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

Value 1 GB = 1 GB; 1MB = 1/1024 GB and 1KB = 1/1048576 GB

Thus, everything in the table is standardized in GBs

2. Creating a desktop example place in the table: ( =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

Thus, you can drag the form into the GB formula , and if there is something that can be fixed, you do this 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 a cool virtual beer.

+1
source

enter image description here 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))))
0
source

This is pretty long, but assuming your value is 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 is specified (bytes). It is not case sensitive (gb, gB, GB, Gb) and the only limitation is the presence of a space after the value (or nothing in the case of bytes)

0
source

I liked Amit's answer, but with bytes instead of bits, and since I did not see the answer in bytes, I am sending it. Also my language 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)
0
source

I would love to share a beer with Abe Gold. Or, if you all could earn me a reputation where I could comment on joining Abe Gold, that would be great too.

Here is the solution that worked for me, building from Abe. It uses standard US formatting (from semicolons to commas) and simply returns the value of the calculated cell if (gb, mb, kb, b) are not found. (paste this text into A2, then copy to where 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)
0
source

More articles:


All Articles