Go Back

Normalize size units to Gigabytes

Read time: 30 minutes

A byte is a unit of memory size and the decimal equivalent of one gigabyte is 1,000,000 bytes.  Other units of computer storage and memory include kilobytes, megabytes, terabytes, petabytes, and many more.   This step by step tutorial will assist all levels of Excel users in normalizing size units into gigabytes.

Figure 1.  Final result:  Normalize size units to gigabytes

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

Syntax of LEFT and RIGHT functions

LEFT extracts a substring from the left side of a text string

=LEFT (text,[num_chars])

RIGHT extracts a substring from the right side of a text string

=RIGHT(text,[num_chars])

  • text – the text string containing the characters or substring we want to extract  
  • num_chars  – Optional; determines the number of characters we want to extract

Syntax of LEN function

LEN returns the length of a text string in number of characters

=LEN(text)

  • text  – the text whose length we want to determine. Spaces are counted as characters

Syntax of MATCH function

MATCH function returns the position of a value in a range

=MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value – a value which we want to find in the lookup_array
  • lookup_array – the range of cells containing the value we want to match
  • [match_type] – optional; the type of match; if omitted, the default value is 1; We use 0 to find an exact match

Setting up our Data

Figure 2.  Sample data to normalize size units to gigabytes

Our table consists of two columns: Input Size (column B) and Size in GB (column C).  Our data in column B contains storage and memory values in different units.

We want to convert the values in column B and normalize them into gigabytes “GB”, regardless of the original units.  For our formula to work, the values in column B must contain the unit in the last two characters of the string. The results will be recorded in column C.  

We will use below table as our guide in the conversion of units, where KB, MB, GB, TB and PB are represented in decimal multiples of bytes.  

Figure 3.  Decimal multiples of bytes

Normalize size units to gigabytes

We want to be able to convert all values in column B into one uniform unit which is GB (gigabytes).  We can do this by using a combination of the LEFT, LEN, RIGHT and MATCH functions. Let us follow these steps:  

Step 1.  Select cell C3

Step 2.  Enter the formula: =LEFT(B3,LEN(B3)-2)/10^((MATCH(RIGHT(B3,2),{"PB","TB","GB","MB","KB"},0)-3)*3)

Step 3.  Press ENTER

Step 4.  Copy the formula in C3 to cells C4:C9 by clicking the “+” icon at the bottom-right corner of cell C3 and dragging it down

Figure 4.  Entering the formula to normalize size units to gigabytes

Our formula extracts the numeric value of the size in column B, then divides it by the corresponding divisor to normalize the given unit into gigabytes.  

The formula has two main components,

  • LEFT(B3,LEN(B3)-2)  extracts the numeric value of the size in column B
    • In order to get the numeric value, the formula extracts the characters from the left of B3, but not including the last 2 characters that represent the unit.  
  • 10^((MATCH(RIGHT(B3,2),{"PB","TB","GB","MB","KB"},0)-3)*3) divisor that converts the unit into gigabytes, represented as a power of 10
    • First, the formula matches the unit in B3 to the array {“PB”,”TB”,”GB”,”MB”,”KB”}
    • In the first example, the unit is KB so MATCH returns the value 5, since KB is the fifth in the list
    • The formula becomes 10^((5-3)*3) or 10^6, which is correct because there are 10^6 KB in 1 GB

Finally, cell C3 is calculated as 1000/10^6 or 0.001.  

For the next two examples bearing the unit “MB”, the MATCH function returns the value 4, making the divisor 10^((4-3)*3 or 10^3.  This corresponds to the conversion factor of 10^3 MB in 1 GB. As a result, the values in cells C4 and C5 are 0.1 and 1, respectively.  

Figure 5.  Converting megabytes to gigabytes

The values in cells B6 and B7 are already in GB, so the values in column C are the same and no conversion is needed.    

Figure 6.  Formula returns same values in gigabytes

Below table shows the final results in normalizing size units to gigabytes by using the LEFT, LEN, RIGHT and MATCH functions in Excel.   

Figure 7.  Output: Normalize size units to gigabytes

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
nsert the data into a pivot table In the pivot table, please provide the following: - Units by Region - Total by Rep - Units by Order Date - Chart demonstrating total units by year
An Excelchat Expert solved this problem in 22 mins!
Hello, I am trying to write a formula on Excel for cumulative tiered pricing and would like some assistance. Here's my example: 1 - 25 units = $100/unit 26 - 50 units = $85/unit 51 - 100 units = $70/unit More than 100 = $55/unit These are example results: 26 units = (25*100)+(1*85) 75 units = (25*100)+(25*85)+(25*70) I am trying to write a formula using sumproduct, but it only works for calculations from 1-25 units, and not above that. Any help on this would be greatly appreciated!
An Excelchat Expert solved this problem in 20 mins!
I have two worksheets with season and style in two columns and units in third. one sheet contains received units and the other contains shipped units. I would like to bring shipped units next received units by season and style. Year Division Div Name Season Style Line Qty Net 2013 Z LAUREN 3 LL103P 300 $13,041 2013 Z LAUREN 3 LL103W 249 $12,599 2013 Z LAUREN 3 LL280W 136 $10,479 Division Season Style Units Shipping Loc Z-LAUREN 3 LL103P 300 BD1 Z-LAUREN 3 LL103W 250 BD1 Z-LAUREN 3 LL280W 136 BD1
An Excelchat Expert solved this problem in 11 mins!
Here are some problems that our users have asked and received explanations on
I need help to figure out how to normalize all my data to a value of 100
Solved by C. S. in 22 mins
ok here is what i'm trying to do.... I'm working on an inventory database. cell D2 has a drop down with my unit size, if it's a 14 or 12oz unit than i want to divide cell G2, which is total number of units by the number of units that come on a pallet so that i can get my true pallet count. this is the best i can do.... =if(D2="14oz","12oz",G2/120,IF(D2={"32oz"},G2/130)) Thanks!
Solved by Z. B. in 15 mins
I want to calculate the number of units that are required to be purchased with a minimum $100 purchase. But the minimum purchase needs to be rounded to be multiples of the number of units that are sold in a package. For example The Unit price is $60 per 1,000 pieces or $0.06 each. The package size is 2500 pieces. So I need to calculate how many packages I have to buy in order to exceed $100 and that enables me to ship full packages. In this case, I know the answer is one package - which would be $150 (clearly over the $100 minimum). How would I write an Excel formula in order to calculate 80,000 different types of units?
Solved by M. Y. in 26 mins

Leave a Comment

avatar