Solved

Excel macro to convert GB, KB > MB

Posted on 2011-02-22
18
4,393 Views
Last Modified: 2012-05-11
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 ?

Thanks in advance for your help.
0
Comment
Question by:croustimiel
  • 6
  • 5
  • 5
  • +1
18 Comments
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
too slow...  got interrupted in the middle, lol...
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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

by:Helen_Feddema
Comment Utility
Copy down so each row has the right row reference
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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

by:croustimiel
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
For the command button click option, this is what the result looks like:
command click version
Hope this helps!

Dave
0
 

Author Comment

by:croustimiel
Comment Utility
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

Thnaks in advance for your reactivity.
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
Comment Utility
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

Open in new window


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

by:croustimiel
Comment Utility
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

by:rspahitz
Comment Utility
#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

by:croustimiel
Comment Utility
I have Excel 2003, I will try your process tomorrow morning.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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

by:croustimiel
Comment Utility
Perfect, exactly what I want.
Thanks a lot.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now