Formula to convert KG MB GB TB to GB in excel

Question

I'm attempting to convert data from an excel sheet that is stored in GB, MB, TB, and KB units. Everything needs to be changed to GB.

Here is the formula I use:

=IF(ISERROR(FIND("GB",H2))=FALSE,VALUE(LEFT(H2,FIND("GB",H2)-1)*1),IF(ISERROR(FIND("TB",H2))=FALSE,VALUE(LEFT(H2,FIND("TB",H2)-1)*1024),VALUE(LEFT(H2,FIND("MB",H2)-1)/1024),VALUE(LEFT(H2,FIND("KB",H2)-1)/1048576)))

I get an error with too many arguments.

Answer 1

Use this array formula:

=LEFT(A1,MIN(IFERROR(FIND({"KB","MB","GB","TB"},A1),1E+99))-1)/INDEX({1048576,1024,1,0.0009765625},MATCH(RIGHT(A1,2),{"KB","MB","GB","TB"},0)) & "GB"

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of enter. If done correctly then excel will put {} around the formula.

---

If your string always ends with the size then you can use this normal formula:

=LEFT(A1,LEN(A1)-2)/INDEX({1048576,1024,1,0.0009765625},MATCH(RIGHT(A1,2),{"KB","MB","GB","TB"},0)) & "GB"

I hope this helps you.