Solved

Excel macro to convert GB, KB > MB

Posted on 2011-02-22
18
5,048 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
[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
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +1
18 Comments
 
LVL 22

Expert Comment

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

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

by:dlmille
ID: 34956468
too slow...  got interrupted in the middle, lol...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

Expert Comment

by:Helen Feddema
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

by:Helen Feddema
ID: 34956498
Copy down so each row has the right row reference
0
 
LVL 22

Expert Comment

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

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

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

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

by:dlmille
ID: 34959142
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
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

Thnaks in advance for your reactivity.
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 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

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

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

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

Expert Comment

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

by:croustimiel
ID: 34972235
Perfect, exactly what I want.
Thanks a lot.
0
 
LVL 42

Expert Comment

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

630 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