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 :-)
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.
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.
Here's another method:
=LEFT(A1,LEN(A1)-2)/10^((IFERROR(MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0),6)-3)*3)
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))))
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With