Excel Trick: Normalizing MB and KB into numbers

Someone brought me some data yesterday where they wanted to graph some data that included sizes of files. The file size in the data file was a mixture of things like “200MB” or “342KB” and so on. Obviously Excel can’t graph that as it is, so I wrote a complex function to convert it to a plain number, which Excel can graph.The function is here, with line-breaks and indentation inserted to make it clearer.

=IF(RIGHT(E2,2)="KB",
  LEFT(E2,(LEN(E2)-2))*1024,
  IF(RIGHT(E2,2)="MB",
    LEFT(E2,(LEN(E2)-2))*1024*1024,
      IF(RIGHT(E2,2)="GB",
        LEFT(E2,(LEN(E2)-2))*1024*1024*1024,
          IF(RIGHT(E2,2)="TB",
            LEFT(E2,(LEN(E2)-2))*1024*1024*1024*1024,
          "ERR"))))

The explanation is basically this.

If the last 2 letters are “KB”, then get a substring that is the length of the string, minus 2 (accounting for the “KB”) and then take that result and multiply it by 1024. Else, look for “MB” and multiply by 1024*1024. And so on. This is just a series of if-then-else cases. If it doesn’t find “KB”, “MB”, “GB”, or “TB”, then it just returns the string “ERR”. it might be sensible to replace that with zero instead. Hopefully someone will get some value out of this.

Excel Conversion Formula