formula to convert KG,MB,GB,TB to GB in excel

Question

i am tryinng to convert the data present in an excel sheet, some in GB, MB, TB, KB. I want to convert everything into 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 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"

Answer 2

The IF for MB was missing:

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

Not that I encourage this kind of formula because it is so obtuse, but that is what was wrong with it. Also, it assumes that the last value HAS to be KB.

Answer 3

=ROUND( LEFT(H2,FIND("B",H2)-2) *1024^(FIND(MID(H2,FIND("B",H2)-1,1),"KMGTPEZY")-3) ,2)&"GB"

The LEFT part is to get the number before ?B, the *1024^ part is to convert it to GB based on the letter before B, and ROUND is optional