Format a mixed column of T, G, M, K postfixed data, in Excel

Objective: This Excel formula converts a file size value formatted with a “T”, “G”, “M”, or “K” postfix (i.e., tera-, giga-, mega-, kilo-), to a single uniform format (e.g., bytes).

This may come in handy if you forget (like me) to pipe linux’s du -sh to sort -n -r, and end up with a long list of human-readable, but difficult to sort, list of file sizes. Converting everything back to bytes, or some other uniform format, then makes sorting much easier.

=IF(RIGHT(A2,1)="T",
    LEFT(A2, LEN(A2)-1)*POWER(2,40),
    IF(RIGHT(A2,1)="G",
       LEFT(A2, LEN(A2)-1)*POWER(2,30),
       IF(RIGHT(A2,1)="M",
          LEFT(A2, LEN(A2)-1)*POWER(2,20),
          IF(RIGHT(A2,1)="K",
             LEFT(A2, LEN(A2)-1)*POWER(2,10)))))

Alternative Formulae

Calculate bytes, and append a “B” postfix (by using Excel’s TEXT function to add formatting)

=IF(RIGHT(A2,1)="T",
    TEXT(LEFT(A2, LEN(A2)-1)*POWER(2,40),"##0.00\B"),
    IF(RIGHT(A2,1)="G",
       TEXT(LEFT(A2, LEN(A2)-1)*POWER(2,30),"##0.00\B"),
       IF(RIGHT(A2,1)="M",
          TEXT(LEFT(A2, LEN(A2)-1)*POWER(2,20),"##0.00\B"),
          IF(RIGHT(A2,1)="K",
             TEXT(LEFT(A2, LEN(A2)-1)*POWER(2,10),"##0.00\B")))))

Calculate kilobytes (by changing the power exponent), and append a “K” postfix

=IF(RIGHT(A2,1)="T",
    TEXT(LEFT(A2, LEN(A2)-1)*POWER(2,30),"##0.00\K"),
    IF(RIGHT(A2,1)="G",
       TEXT(LEFT(A2, LEN(A2)-1)*POWER(2,20),"##0.00\K"),
       IF(RIGHT(A2,1)="M",
          TEXT(LEFT(A2, LEN(A2)-1)*POWER(2,10),"##0.00\K"),
          IF(RIGHT(A2,1)="K",
             TEXT(LEFT(A2, LEN(A2)-1)*POWER(2,0),"##0.00\K")))))

We can also change the power base to use the decimal system.

=IF(RIGHT(A22,1)="T",
    LEFT(A22, LEN(A22)-1)*POWER(10,12),
    IF(RIGHT(A22,1)="G",
       LEFT(A22, LEN(A22)-1)*POWER(10,9),
       IF(RIGHT(A22,1)="M",
          LEFT(A22, LEN(A22)-1)*POWER(10,6),
          IF(RIGHT(A22,1)="K",
             LEFT(A22, LEN(A22)-1)*POWER(10,3)))))