Excel – Converting Byte Units to Gb

The previous two posts covered how to extract numbers and then letters from a cell. This post brings it altogether into a function that can convert a number of bytes with a unit to gigabytes. In the past I have done this with VBA macro functions such as the one inserted below. The issue with using VBA macros is it can limit the portability of a spreadsheet. Since hackers started using VBA macros to write malware and spyware many organizations have put policies in place that block the execution of VBA macros or require macros signatures registering the functions to the organization. These limit the ability of a spreadsheet to be distributed and used without significant steps to get approvals and registrations within a company’s policies.

Function GetNumber(rWhere As Variant, Optional Accept_Decimals As Boolean, Optional Accept_Negative As Boolean) As String

Dim CharPostion As Integer, i As Integer, StringLength As Integer
Dim sText As String, mchNeg As String, mchDec As String
Dim ThisNum As String
Dim vChar, vChar2
mchNeg = vbNullString
mchDec = vbNullString

Select Case TypeName(rWhere)
    Case Is = "Range"
        ' Get the text from the supplied range
        sText = Trim(rWhere.Text)
    Case Else
        sText = Trim(rWhere)
End Select

If Accept_Decimals = True Then
    mchDec = "."
End If
If Accept_Negative = True Then
    mchNeg = "-"
End If
    
StringLength = Len(sText)
For CharPostion = StringLength To 1 Step -1
    vChar = Mid(sText, CharPostion, 1)
        If IsNumeric(vChar) Or vChar = mchNeg Or vChar = mchDec Then
                i = i + 1
                ThisNum = Mid(sText, CharPostion, 1) & ThisNum
            If IsNumeric(ThisNum) Then
                If CDbl(ThisNum) < 0 Then Exit For
            Else
                ThisNum = Replace(ThisNum, Left(ThisNum, 1), "", , 1)
            End If
        End If
    If i = 1 And ThisNum <> vbNullString Then ThisNum = CDbl(Mid(ThisNum, 1, 1))
Next CharPostion
'GetNumber = CDbl(ThisNum)
GetNumber = ThisNum
End Function

Function BytesToGb(Where As Range) As Double

Dim NumMB As Double

' Get the text from the supplied range
strWhere = Trim(Where.Text)

'Extract the number and unit from the text
'NumPart = CDbl(Mid(strWhere, 1, (InStr(1, strWhere, "b", vbTextCompare) - 2)))
NumPart = GetNumber(Where, True, True)
NumUnit = LCase(Trim(Replace(Where, NumPart, "")))
'Mid(strWhere, InStr(1, strWhere, "b", vbTextCompare) - 1, Len(strWhere) - (InStr(1, strWhere, "b", vbTextCompare) - 2))

'Use unit to convert the number part to MB value
Select Case NumUnit
    Case "kb", "k"
        NumMB = NumPart / 1024 ^ 2
    Case "mb", "m"
        NumMB = NumPart / 1024
    Case "gb", "g"
        NumMB = NumPart
    Case "tb", "t"
        NumMB = NumPart * 1024
    Case Else
        NumMB = NumPart / 1024
End Select

' Return the MBs
BytesToGb = CDbl(NumMB)
End Function

Rather than use a VBA macro function like the one above since with newer versions of Excel we now have more available array functions this can be done using the formulae discussed in the previous two posts combined into a named lambda function.

'LAMBDA(vX,IFERROR(VALUE(TEXTJOIN("",TRUE,FILTER(MID(vX,SEQUENCE(1,LEN(vX)),1),ISNUMBER(VALUE(MID(vX,SEQUENCE(1,LEN(vX)),1)))+(MID(vX,SEQUENCE(1,LEN(vX)),1)="."),"")))/10^((MATCH(LEFT(UPPER(TRIM(TEXTJOIN("",TRUE,FILTER(MID(vX,SEQUENCE(1,LEN(vX)),1),NOT(ISNUMBER(VALUE(MID(vX,SEQUENCE(1,LEN(vX)),1))))*(MID(vX,SEQUENCE(1,LEN(vX)),1)<>"."),"")))),1),{"P","T","G","M","K"},0)-3)*3),""))

Formula Explanation

For the explanation of the formulas on either side of the /10^ please see the pervious two posts. So let’s make it into a function using the Name Manager under formulas with the Lambda function. You may be familiar with creating named ranges. Under the Formula menu item in Excel you can select a range of cells and choose define name so that you can refer to it else where. But what you can also do is create a name that behaves like a function. Open Name Manager and add a name BytesToGB and paste into the reference field the formula above. Then you can reuse this formula against any reference just by using =BytesToGB(Ref).

In this function we divide the number extracted from the cell by the number created when we match the letters extracted to the array {“P”,”T”,”G”,”M”,”K”} subtracting 3. So for Petabytes 1 minus 3 would equal negative 2 time 3 would equal negative 6. Thus dividing a number by 10 raised to the power of -6 would add six zeros to it arriving at the number of gigabytes. Similarly for Terabytes 2 minus 3 would equal -1 multipled by 3 would be -3 and would result in 3 zeros or decimal places being added to the number converting it to gigabytes.