Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel macro to convert GB, KB > MB

Posted on 2011-02-22
Medium Priority
5,251 Views
Hello,

I have a excel file with a structure like this :

Name            Memory
A                    12 GB
B                    5.7 GB
C                   300 KB

And I want the file like this structure :

Name            Memory (MBytes)
A                    12 000
B                    5700
C                    0.300

Then in the second column I want the converted value without the unit.
How can I do this in MS Excel ? Macro ?

0
Question by:croustimiel
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5
• 5
• +1

LVL 22

Expert Comment

ID: 34956426
A simple version would be something like this...

In cell C2, put the following formula:

=IF(RIGHT(B2,2)="KB",VALUE(LEFT(B2, LEN(B2)-3))/1000,IF(RIGHT(B2,2)="GB",VALUE(LEFT(B2, LEN(B2)-3))*1000,VALUE(LEFT(B2, LEN(B2)-3))))

Copy it down as needed and hide column B
0

LVL 42

Expert Comment

ID: 34956464
See attached.  Simple table with conversion factors, a vlookup against the units (last two characters) and presto - converted.  I did this on spreadsheet column A, and also a range name A

Let me know if this is what you're looking for...

Dave
conversion.xls
0

LVL 42

Expert Comment

ID: 34956468
too slow...  got interrupted in the middle, lol...
0

LVL 31

Expert Comment

ID: 34956492
This formula will do it:

=IF(RIGHT(B2,2)="GB",LEFT(B2,LEN(B2)-3)*1000,LEFT(B2,LEN(B2)-3)/1000)
0

LVL 31

Expert Comment

ID: 34956498
Copy down so each row has the right row reference
0

LVL 22

Expert Comment

ID: 34956500
lol...but yours uses the correct conversion of 1024 whereas I use the slightly incorrect 1000 for multiplying and dividing :)
I also like the way you search for the space to get the value...combination of both of our solutions is good.
0

LVL 42

Expert Comment

ID: 34956514
Agreed - good, quick solution.  I like using conversion tables rather than embedding in formulas.  Yes 1024 (remembered from my "Computer Science" days, lol :)

Dave
0

Author Comment

ID: 34958443
Hello,
With all these method, i need a new column, one for the original value and an other for the result (where there is the formula).
Is it possible to have a button where we click in order to convert, with the formula you give, the original value.

It will be perfect ....
0

LVL 42

Expert Comment

ID: 34959074
The spreadsheet I posted had a column for original value and the other for result.  The attached perhaps clarifies that.  The YELLOW cells you can enter your original values, and the corresponding cell to the right shows the conversion.  There's also a conversion table you can enhance if you want to add more conversion factors.

I'm not 100% sure what you're asking for in your second sentence.  But, I did create a button you can click that prompts you for input and then communicates the converted value.

PS - you can also go to GOOGLE and ask "how many MB in 35 GB" and it will give you the result - see this: http://www.google.com/search?q=how+many+MB+in+35+GB&rlz=1I7GGLL_en&ie=UTF-8&oe=UTF-8&sourceid=ie7

Cheers,

Dave
conversion-r2.xls
0

LVL 42

Expert Comment

ID: 34959142
For the command button click option, this is what the result looks like:

Hope this helps!

Dave
0

Author Comment

ID: 34961917
In fact I want that I click on the button and the column value is REPLACED by the converted values.
Not a msgbox as you attached. I don't know if I'm clear.

I have this on the file :
Name            Memory
A                    12 GB
B                    5.7 GB
C                   300 KB

I click on a"Convert" button and after the second column is replaced by the conversion result, all in MB:
Name            Memory
A                    12 000
B                    5700
C                    0.300

0

LVL 22

Accepted Solution

rspahitz earned 2000 total points
ID: 34962427
If this is a one-time conversion, then I highly recommend doing the above formula, then copy column C and paste-special-Values into column B then clear column C.

However, if you will be doing this over and over and, for whatever reason, don't want to hide column B (or push it somewhere else like a different sheet, then the only real answer is some VBA/macro.

To handle that, the code will look as follows:

``````Sub ConvertByteTags()
' This code will navigate down column B, starting at row 2
' and replace every GB, KB and MB value with its equivalent extended value
Dim iRow As Integer
Dim strValue As String
Dim strSizeType As String
Dim dblNewValue As Double

Const ConversionFactor As Integer = 1000 ' 1024

iRow = 2
Do
strValue = Cells(iRow, 2).Value
If strValue = "" Then
Exit Do
End If

strSizeType = Right(strValue, 2)
Select Case strSizeType
Case "TB" ' terrabytes
dblNewValue = Val(strValue) * ConversionFactor * ConversionFactor
Case "GB" ' gigabytes
dblNewValue = Val(strValue) * ConversionFactor
Case "MB" ' megabytes
dblNewValue = Val(strValue) * 1
Case "KB" ' kilobytes
dblNewValue = Val(strValue) / ConversionFactor
Case Else
dblNewValue = Val(strValue) / ConversionFactor / ConversionFactor
End Select
Cells(iRow, 3).Value = dblNewValue
iRow = iRow + 1
Loop
MsgBox "Cells converted into MB", vbOKOnly Or vbInformation
End Sub
``````

To use it, go to the VB area (Alt-F11), insert a new module (menu Insert | Module), and paste the above into that window.
Back to Excel, Alt-F8 and you should see "ConvertByteTags".  double-click it to run it.

(Oh, for now I put it into column C so you can test it.  When you're ready, change the line near the end to reference column 2:

Cells(iRow, 2).Value = dblNewValue

Also, to put the correct values in, you will need to divide by 1024 rather than 1000, so change the line near the top to this:

Const ConversionFactor As Integer = 1024
0

Author Comment

ID: 34962962
I tried your VBA, it's exactly what I want.

1- Whereas how can I add a button on my Sheet which execute this script ?
2- And how can I execute this script UNTIL row 29, I don't want it convert row 30 ?

Thanks a lot for your help, it's near the end
0

LVL 22

Expert Comment

ID: 34963039
#2:
Before the line "Loop" add this:

If iRow >29 Then
Exit Do
End If

#1:
To add a button, you will need to enable the Developer tab in Excel Options
which version are you using?  For 2010, go to Customize Ribbo, then in the far right List, select "Developer"  (if it's not there, locate it on left list and Add it to the right.

Next, after you close the options, you should have a new Developer tab along the top.  Select that and the in the Controls gorup box, select Insert and Pick the button in the top left corner.  After selected, When you try to click on the form, it will ask which macro you want to assign to that button.  Select the ConvertByteTags ones created in the previous message and click OK.

Click on the form and the button will be ready to go.  To move it, right-click it and drag it to the desired place; to activate it, click elsewhere on the form.
0

Author Comment

ID: 34964678
I have Excel 2003, I will try your process tomorrow morning.
0

LVL 22

Expert Comment

ID: 34964749
In 2003, you can add the Forms toolbar (I think it's menu View | Toolbars)
From there you can add the button the same way.
0

Author Closing Comment

ID: 34972235
Perfect, exactly what I want.
Thanks a lot.
0

LVL 42

Expert Comment

ID: 34973350
Please ensure you state your question properly - up front, rather than changing your needs after 2 days.  There was no requirement to replace formulas with values in place at the beginning of this query.  A couple of us responded and met your question exactly, early in the process.

Cheer,

Dave
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
###### Suggested Courses
Course of the Month11 days, 13 hours left to enroll