Excel: Unit Conversion (MB, GB, KB etc.)

Here's another method:

  • Assumes the value in A1 is valid
  • Works from PB to KB (or nothing) and is easily extensible if necessary
  • As written normalizes to GB, but that is easily changed.
  • Assumes the UNITS are the last two characters of the string, if present
 =LEFT(A1,LEN(A1)-2)/10^((IFERROR(MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0),6)-3)*3)

In B1 enter:

=IF(RIGHT(A1,2)="GB",--MID(A1,1,FIND(" ",A1)-1),--MID(A1,1,FIND(" ",A1)-1)/1000)

and copy down:

enter image description here

EDIT#1:

To handle GB. MB, KB, B, and no suffix, use this formula:

=IF(RIGHT(A1,2)="GB",--MID(A1,1,FIND(" ",A1)-1),IF(RIGHT(A1,2)="MB",--MID(A1,1,FIND(" ",A1)-1)/1000,IF(RIGHT(A1,2)="KB",--MID(A1,1,FIND(" ",A1)-1)/1000000,IF(RIGHT(A1,1)="B",--MID(A1,1,FIND(" ",A1)-1)/1000000000,A1/1000000000))))

enter image description here

Tags:

Excel

Recent Posts