Converting File sizes from bytes to Kb or Mb

Question

I have got a large amount of rows with different values in it, from 1000s to 1,000,000s.

This is data that has come across to me in bytes and i need to convert it to Kb or Mb respectively.

I could divide all the values by 1M and get all the data en Mb but i want to have the data in kb and Mb.

I tried using the MOD function but that wont do the trick as all the numbers are going to be divisible by 1K and 1M so i am a bit stuck!

here is a sample of the data that i get:

16000000
220000
2048000
2048000
230000
16000000
230000
16000000
220000
230000

so what i need is that if the cell contains 6 zeros then divide by 1M or if the cell contains 3 zeros devide by 1000.

I will add concatenation to each individual result in order to get the data differentiated.

Answer 1

This is the function you are looking for :

Public Function SizeInStr(ByVal Size_Bytes As Double) As String
    Dim TS()
    ReDim TS(4)
    TS(0) = "b"
    TS(1) = "kb"
    TS(2) = "Mb"
    TS(3) = "Gb"
    TS(4) = "Tb"

Dim Size_Counter As Integer
Size_Counter = 0

    If Size_Bytes <= 1 Then
        Size_Counter = 1
    Else
        While Size_Bytes > 1
            Size_Bytes = Size_Bytes / 1000
            ''Or
            'Size_Bytes = Size_Bytes / 1024
            Size_Counter = Size_Counter + 1
        Wend
    End If

    SizeInStr = Format(Size_Bytes * 1000, "##0.0#") & " " & TS(Size_Counter - 1)
    ''Or
    'SizeInStr = Format(Size_Bytes * 1024, "##0.0#") & " " & TS(Size_Counter - 1)
End Function

Use it simply like this :

Private Sub Test_SizeInStr()
   MsgBox SizeInStr(1000000)
End Sub

Answer 2

If you are looking for some VBA code you might use this Function:

Public Function yourFunction(ByVal number)
  If number > 1000000 Then
    'number as MB
    yourFunction = number / 1000000
    'or use the following to add MB
    'yourFunction = (number / 1000000) & " Mb"
  Else
    'number in kB
    yourFunction = number / 1000
    'or use the following to add kB
    'yourFunction = (number / 1000) & " Kb"
  End if
End Function

If you are looking for an Excel-Formula you might use this Function (original value in A1), put this formula in another column (for example column B)

=IF(A1>1000000,A1/1000000,A1/1000)

or with concatenation:

=IF(A1>1000000,CONCATENATE(A1/1000000," Mb"),CONCATENATE(A1/1000," Kb"))