Explanation

Important: This formula assumes that units are the last 2 characters of the text value that includes both a number and a unit of measure.

This formula 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 divides the number by the appropriate divisor to normalize to Gigabytes. The divisor 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 the unit with a hard-coded array constant, {“PB”,“TB”,“GB”,“MB”,“KB”}:

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

The result from MATCH is the position of the unit in the array constant. For example, for the formula in C5, the unit is “KB”, so the result 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

Binary standard formula

Computers use the binary number system to store and report data size, but the prefixes like “kilo”, “mega”, “giga”, etc. are based on the metric system. It’s a confusing topic, but using decimal units for storage on a computer isn’t really correct, and the discrepancy increases as units get larger. The formula below will normalize to binary units.

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

With this formula, you are technically getting Gibibytes (GiB), not Gigabytes. More information here and here .

Explanation

By definition, the nth root of a number can be calculated by raising that number to the power of 1/n. Exponents are entered using the exponentiation operator (^), with a number on the left and power on the right. So, in this example we get the numbers from column B and powers from column C:

=B5^(1/C5)

With the POWER function

The POWER function is another way to perform exponentiation in Excel. To get the nth root of a number with POWER, use the number with 1/n for the power argument:

=POWER(number,1/n)

So for the example shown, the formula in D5 would be:

=POWER(B5,1/C5)