Convert MB, GB, KB to bytes

revelation.now

New Member
Joined
Dec 11, 2009
Messages
10
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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

New Member
Joined
Dec 11, 2009
Messages
10
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
Office Version
2013
Platform
Windows
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
Code:
=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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
In a Module, paste this. Use as call as show in the Test sub or as a UDF as commented.
Code:
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,405
Office Version
365
Platform
Windows
Here is another possible option:

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;background-color: #FAFAFA;;">36.96 GB</td><td style=";">39685497815</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;background-color: #FAFAFA;;">731 KB</td><td style="text-align: right;;">748544</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;background-color: #FAFAFA;;">1.74 GB</td><td style="text-align: right;;">1868310774</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;background-color: #FAFAFA;;">6.74 GB</td><td style=";">7237019894</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;background-color: #FAFAFA;;">1.67 GB</td><td style="text-align: right;;">1793148846</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #333333;background-color: #FAFAFA;;">16.1 MB</td><td style="text-align: right;;">16882073.6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;background-color: #FAFAFA;;">1.22 GB</td><td style="text-align: right;;">1309965025</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #333333;background-color: #FAFAFA;;">106.87 MB</td><td style="text-align: right;;">112061317.1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #333333;background-color: #FAFAFA;;">895.69 MB</td><td style="text-align: right;;">939199037.4</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #333333;background-color: #FAFAFA;;">41.99 MB</td><td style="text-align: right;;">44029706.24</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="color: #333333;background-color: #FAFAFA;;">2.76 MB</td><td style="text-align: right;;">2894069.76</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="color: #333333;background-color: #FAFAFA;;">176.7 MB</td><td style="text-align: right;;">185283379.2</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="color: #333333;background-color: #FAFAFA;;">310.9 MB</td><td style="text-align: right;;">326002278.4</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="color: #333333;background-color: #FAFAFA;;">423.93 KB</td><td style="text-align: right;;">434104.32</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="color: #333333;background-color: #FAFAFA;;">5.84 MB</td><td style="text-align: right;;">6123683.84</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="color: #333333;background-color: #FAFAFA;;">390.41 KB</td><td style="text-align: right;;">399779.84</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="color: #333333;background-color: #FAFAFA;;">494.92 MB</td><td style="text-align: right;;">518961233.9</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="color: #333333;background-color: #FAFAFA;;">44.86 MB</td><td style="text-align: right;;">47039119.36</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="color: #333333;background-color: #FAFAFA;;">2.65 GB</td><td style=";">2845415834</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="color: #333333;background-color: #FAFAFA;;">3.56 GB</td><td style=";">3822520893</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="color: #333333;background-color: #FAFAFA;;">46.37 MB</td><td style="text-align: right;;">48622469.12</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="color: #333333;background-color: #FAFAFA;;">180.77 MB</td><td style="text-align: right;;">189551083.5</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="color: #333333;background-color: #FAFAFA;;">41.75 MB</td><td style="text-align: right;;">43778048</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="color: #333333;background-color: #FAFAFA;;">7.73 MB</td><td style="text-align: right;;">8105492.48</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,FIND(<font color="Red">" ",A1</font>)</font>)*1024^LOOKUP(<font color="Blue">RIGHT(<font color="Red">A1,2</font>),{"GB","KB","MB"},{3,1,2}</font>)</td></tr></tbody></table></td></tr></table><br />
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,405
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 11, 2009
Messages
10
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,098,871
Messages
5,465,192
Members
406,416
Latest member
Revolution_72

This Week's Hot Topics

Top