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