Normalize size units to Gigabytes

Normalize size units to Gigabytes

Generic formula 

=LEFT(A1,LEN(A1)-2)/10^((MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0)-3)*3)

Related formulas 

Split text and numbers

Split numbers from units of measure

Explanation

To normalize units to Gigabytes (or megabytes, kilobytes, etc.) you can use a clever formula based the MATCH, LEFT, and RIGHT functions.

In the example shown, the formula in C5 is:

=LEFT(B5,LEN(B5)-2)/10^((MATCH(RIGHT(B5,2),{"PB","TB","GB","MB","KB"},0)-3)*3)

How this formula works

Note: this formula assumes that units are the last 2 characters of the string that includes both a number and a unit of measure.

This elegant formula shows off what can be done with built-in Excel functions and a little cleverness. It works because digital units have a "power of 10" relationship.

At the core, this formula separates the number part of the size from the unit, then pides the number by the appropriate pisor to normalize to Gigabytes. The pisor is calculated as a power of 10, so the formula reduces to this:

=number/10^power

To get the number, the formula extracts all characters from the left up to but not including the units:

LEFT(B5,LEN(B5)-2)

To get "power", the formula matches on the unit in a hard-coded array constant:

MATCH(RIGHT(B5,2),{"PB","TB","GB","MB","KB"},0)

Which returns the position of the unit in the array constant. For example, for the formula in C5, the unit is "KB", so the position is 5. This result is adjusted by subtracting 3, then multiplying the result by 3, which yields 6 as the power, which is used as the exponent to calculate the correct result in gigabytes:

=900/10^6=900/1000000=0.0009

Normalize size units to Gigabytes

Generic formula 

=LEFT(A1,LEN(A1)-2)/10^((MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0)-3)*3)

Related formulas 

Split text and numbers

Split numbers from units of measure

Explanation

To normalize units to Gigabytes (or megabytes, kilobytes, etc.) you can use a clever formula based the MATCH, LEFT, and RIGHT functions.

In the example shown, the formula in C5 is:

=LEFT(B5,LEN(B5)-2)/10^((MATCH(RIGHT(B5,2),{"PB","TB","GB","MB","KB"},0)-3)*3)

How this formula works

Note: this formula assumes that units are the last 2 characters of the string that includes both a number and a unit of measure.

This elegant formula shows off what can be done with built-in Excel functions and a little cleverness. It works because digital units have a "power of 10" relationship.

At the core, this formula separates the number part of the size from the unit, then pides the number by the appropriate pisor to normalize to Gigabytes. The pisor is calculated as a power of 10, so the formula reduces to this:

=number/10^power

To get the number, the formula extracts all characters from the left up to but not including the units:

LEFT(B5,LEN(B5)-2)

To get "power", the formula matches on the unit in a hard-coded array constant:

MATCH(RIGHT(B5,2),{"PB","TB","GB","MB","KB"},0)

Which returns the position of the unit in the array constant. For example, for the formula in C5, the unit is "KB", so the position is 5. This result is adjusted by subtracting 3, then multiplying the result by 3, which yields 6 as the power, which is used as the exponent to calculate the correct result in gigabytes:

=900/10^6=900/1000000=0.0009