Forum Discussion
Solved
File size unit conversion and calculation
Hi everyone,
I would like standardize the "Usage" Column in MB and turns it into "Number" thus let me do some calcuation .. How can I achieve it ? Thanks !
Let's say the usage data are in B2 and down.
If you use 1 MB = 1024 KB, enter this formula in C2:
=LEFT(B2,LEN(B2)-3)*IF(RIGHT(B2,2)="KB",1/1024,IF(RIGHT(B2,2)="GB",1024,1))
If you use 1 MB = 1000 KB, it should be
=LEFT(B2,LEN(B2)-3)*IF(RIGHT(B2,2)="KB",1/1000,IF(RIGHT(B2,2)="GB",1000,1))
Fill down.
Just to prove there is nothing that can't be made more complicated with due effort:
= LET( value, TEXTBEFORE(Usage, " "), unit, TEXTAFTER(Usage, " "), multiplier, SWITCH(unit, "KB", 1, "MB", 1000, "GB", 10^6, 0), EXPAND(value * multiplier, ,2, "KB") )- This formula should work:
=1*INDEX(TEXTSPLIT(A2," "),,1) Let's say the usage data are in B2 and down.
If you use 1 MB = 1024 KB, enter this formula in C2:
=LEFT(B2,LEN(B2)-3)*IF(RIGHT(B2,2)="KB",1/1024,IF(RIGHT(B2,2)="GB",1024,1))
If you use 1 MB = 1000 KB, it should be
=LEFT(B2,LEN(B2)-3)*IF(RIGHT(B2,2)="KB",1/1000,IF(RIGHT(B2,2)="GB",1000,1))
Fill down.
- Awsome ! Thanks a lot !
