Excel formula: 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)

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

Author

Dave Bruns

Related formulas

Split text and numbers

To separate text and numbers, you can use a formula based on the FIND function, the MIN function, and the LEN function with the LEFT or RIGHT function, depending on whether you want to extract the text or the number. In the example shown, the...

Split numbers from units of measure

Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if you use or adapt. Sometimes you encounter data that mixes units directly with numbers (i.e...

Related functions

Excel MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX...

Excel LEFT Function

The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".

Excel RIGHT Function

The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, RIGHT("apple",3) returns "ple".

Good links

Stackoverflow.com answer by Ron Rosenfeld

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.