Note: for simplicity, we are using decimal (base 10) values, but there is a binary standard as well. See below.
Important: this formula assumes that units are the last 2 characters of the string 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:
To get the number, the formula extracts all characters from the left up to but not including the units:
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:
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 a decimal size 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.
Overview The formula looks complex, but the mechanics are in fact quite simple. As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the position, you can use other...
Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, Excel will treat the numbers in this format as text, and you won't be able to perform math operations on such values. To split a number from...
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, MATCH is combined...
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.