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

In Software Engineering, Snippet

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)))))```