Convert MB, GB, KB to bytes

[revelation.now]

Hi everyone. One of our partners don't understand how to usefully present data, and their equipment is generating data usage reports into csv/xls, however are displaying data values in a mix of GB's, MB's and KB's.

We need to sort and compare this data, and its absolutely useless in this format. We need need all values in a comparative metric, like bytes.

Is there a built in function to accomplish this, or does someone have an idea of what kind of parsing function I would need to run on this? There are multiple thousands of values that need to be converted per sheet.

 

[Kenneth Hobson]

Number conversions can be done. The tricky part is the sting part and if commas exist. Please post all the possible entry string values.

 

[revelation.now]

Here is our 'bytes column'

This would be an easier task, except there is a decimal point in the middle of the data value, otherwise I could do a text search and replace. There is also a Host column, and that contains decimal points that can't be parsed out, so I can't even search and replace out the decimal points.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]

Bytes[/TD]
[/TR]
[TR]
[TD]36.96 GB[/TD]
[/TR]
[TR]
[TD]731 KB[/TD]
[/TR]
[TR]
[TD]1.74 GB[/TD]
[/TR]
[TR]
[TD]6.74 GB[/TD]
[/TR]
[TR]
[TD]1.67 GB[/TD]
[/TR]
[TR]
[TD]16.1 MB[/TD]
[/TR]
[TR]
[TD]1.22 GB[/TD]
[/TR]
[TR]
[TD]106.87 MB[/TD]
[/TR]
[TR]
[TD]895.69 MB[/TD]
[/TR]
[TR]
[TD]41.99 MB[/TD]
[/TR]
[TR]
[TD]2.76 MB[/TD]
[/TR]
[TR]
[TD]176.7 MB[/TD]
[/TR]
[TR]
[TD]310.9 MB[/TD]
[/TR]
[TR]
[TD]423.93 KB[/TD]
[/TR]
[TR]
[TD]5.84 MB[/TD]
[/TR]
[TR]
[TD]390.41 KB[/TD]
[/TR]
[TR]
[TD]494.92 MB[/TD]
[/TR]
[TR]
[TD]44.86 MB[/TD]
[/TR]
[TR]
[TD]2.65 GB[/TD]
[/TR]
[TR]
[TD]3.56 GB[/TD]
[/TR]
[TR]
[TD]46.37 MB[/TD]
[/TR]
[TR]
[TD]180.77 MB[/TD]
[/TR]
[TR]
[TD]41.75 MB[/TD]
[/TR]
[TR]
[TD]7.73 MB
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Is the data that "appears" to be on 2 lines, actually that way ??

36.96
GB
7.73
MB

If so, is it separated by ALT Enter ?
Can you please clarify ?

I'd also suggest a helper column might do the trick with the formula, depending how accurate you want it.
Generally accepted as 1000 for 1 kilobyte but is actually 1024, but you can change the formula to suit

=IF(RIGHT(A2,2)="GB",LEFT(A2,FIND(" ",A2,1))*1000000000,IF(RIGHT(A2,2)="MB",LEFT(A2,FIND(" ",A2,1))*1000000,LEFT(A2,FIND(" ",A2,1))*1000))

 

[Kenneth Hobson]

In a Module, paste this. Use as call as show in the Test sub or as a UDF as commented.

Sub Test_ConvertToBytes()
  MsgBox ConvertToBytes("36.96 GB")
  MsgBox ConvertToBytes("36.96 GB", 0.01)
End Sub

'=ConvertToBytes(A1)
'=ConvertToBytes(A1, 0.01)
Function ConvertToBytes(aString As String, _
  Optional iRound As Double = 1) As Double
  
  Dim d As Double
  d = NumberPart(aString)
  If Len(CStr(d)) = Len(aString) Then
    ConvertToBytes = -1
    Exit Function
  End If
  
  Select Case True
    Case InStr(aString, "KB") > 0
      d = d * 2 ^ 10
    Case InStr(aString, "MB") > 0
      d = d * 2 ^ 20
    Case InStr(aString, "GB") > 0
      d = d * 2 ^ 30
    Case InStr(aString, "TB") > 0
      d = d * 2 ^ 40
    Case InStr(aString, "PB") > 0
      d = d * 2 ^ 50
    Case InStr(aString, "EB") > 0
      d = d * 2 ^ 60
    Case InStr(aString, "ZB") > 0
      d = d * 2 ^ 70
    Case InStr(aString, "YB") > 0
      d = d * 2 ^ 80
    Case Else
      d = -1
  End Select
  
  ConvertToBytes = WorksheetFunction.mRound(d, iRound)
End Function


Sub Test_NumberPart()
  MsgBox NumberPart("36.96 KB")
End Sub

' formula, http://office.microsoft.com/en-us/excel-help/extracting-numbers-from-alphanumeric-strings-HA001154901.aspx
' Modified by Kenneth Hobson
Function NumberPart(aString As String) As Double
  Dim s As String, i As Integer, mc As String, mc2 As String
  aString = Replace(aString, ",", "")
  For i = 1 To Len(aString)
    mc = Mid(aString, i, 1)
    mc2 = ""
    If i <> Len(aString) Then mc2 = Mid(aString, i + 1, 1)
    If Not IsNumeric(mc2) Then mc2 = ""
    If Asc(mc) >= 48 And Asc(mc) <= 57 _
      Or (mc = "-" And mc2 <> "") _
      Or (mc = "." And mc2 <> "") _
      Then s = s & mc
  Next i
  NumberPart = s
End Function

 

[FormR]

Here is another possible option:

Excel 2012
	A	B
1	36.96 GB	39685497815
2	731 KB	748544
3	1.74 GB	1868310774
4	6.74 GB	7237019894
5	1.67 GB	1793148846
6	16.1 MB	16882073.6
7	1.22 GB	1309965025
8	106.87 MB	112061317.1
9	895.69 MB	939199037.4
10	41.99 MB	44029706.24
11	2.76 MB	2894069.76
12	176.7 MB	185283379.2
13	310.9 MB	326002278.4
14	423.93 KB	434104.32
15	5.84 MB	6123683.84
16	390.41 KB	399779.84
17	494.92 MB	518961233.9
18	44.86 MB	47039119.36
19	2.65 GB	2845415834
20	3.56 GB	3822520893
21	46.37 MB	48622469.12
22	180.77 MB	189551083.5
23	41.75 MB	43778048
24	7.73 MB	8105492.48
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1))*1024^LOOKUP(RIGHT(A1,2),{"GB","KB","MB"},{3,1,2})

 

[FormR]

Here is another possible option:

This is probably better than my first suggestion.

=LEFT(A1,FIND(" ",A1))*1024^MATCH(RIGHT(A1,2),{"KB","MB","GB"},0)

 

[revelation.now]

This is probably better than my first suggestion.

=LEFT(A1,FIND(" ",A1))*1024^MATCH(RIGHT(A1,2),{"KB","MB","GB"},0)

FormR, sir, you are a genius!

Kenneth was also on the right track. Those were all individual cells that somehow got wrapped when I posted the data, but inline beats macros.

Thanks heaps everyone!