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.

Bytes

36.96 GB

731 KB

1.74 GB

6.74 GB

1.67 GB

16.1 MB

1.22 GB

106.87 MB

895.69 MB

41.99 MB

2.76 MB

176.7 MB

310.9 MB

423.93 KB

5.84 MB

390.41 KB

494.92 MB

44.86 MB

2.65 GB

3.56 GB

46.37 MB

180.77 MB

41.75 MB

7.73 MB

<tbody>
</tbody>

 

[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!