Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5494
  • Last Modified:

Excel macro to convert GB, KB > MB

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
croustimiel
Asked:
croustimiel
  • 6
  • 5
  • 5
  • +1
1 Solution
 
rspahitzCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
too slow...  got interrupted in the middle, lol...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Helen FeddemaCommented:
This formula will do it:

=IF(RIGHT(B2,2)="GB",LEFT(B2,LEN(B2)-3)*1000,LEFT(B2,LEN(B2)-3)/1000)
0
 
Helen FeddemaCommented:
Copy down so each row has the right row reference
0
 
rspahitzCommented:
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
 
dlmilleCommented:
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
 
croustimielAuthor Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
For the command button click option, this is what the result looks like:
command click version
Hope this helps!

Dave
0
 
croustimielAuthor Commented:
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
 
rspahitzCommented:
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
 
croustimielAuthor Commented:
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
 
rspahitzCommented:
#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
 
croustimielAuthor Commented:
I have Excel 2003, I will try your process tomorrow morning.
0
 
rspahitzCommented:
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
 
croustimielAuthor Commented:
Perfect, exactly what I want.
Thanks a lot.
0
 
dlmilleCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now