Having trouble using Excel? You want to normalize size units to Gigabytes but do not know how to? Then, here’s the complete guide to your problem.

Normalize size units to Gigabytes

Formula

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

Explanation

Here is the explanation that how we can normalize size units to Gigabytes.

Example 1

If you want to normalize size units to Gigabytes while using Excel, you can apply this formula based on MATCH, LEFT, and RIGHT functions.

Figure 1: Normalize size units to Gigabytes in Excel by the simple formula

In mentioned above example, the formula is:

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

Important note

The assumption of this formula is that the last 2 characters include:
A number
A unit of measure

Yes, both will include for the last 2 characters.
This simple formula cleverly completes the mission without any long-term usage of functions. If you are thinking that, why this formula works the best? This is because digital units have a power of 10 relationship that helps to separate the number part from the unit. Then it divides the number with exact divisor to normalize size units to Gigabytes. As we are calculating thereby the power of 10, our new formula would become:

=number/10^power

Getting the exact number, the formula with no units from the left up would be:

=LEFT(B5,LEN(B5)-2)

If you want to get the power, the formula would become:

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

By using this formula one can get the position of the unit in an array constant.

Example 2

Let’s take another example, that for the formula in C5, the unit we have is KB which gives us the position 5. We can adjust this result by subtracting 3 and then multiplying it by 3. It will give us the power of 6 which is the exponent that will calculate the gigabytes correctly.

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

So, this is how one can easily normalize size units to gigabytes in excel as a pro.