• Status: Solved
• Priority: Medium
• Security: Public
• Views: 5810

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 ?

0
croustimiel
• 6
• 5
• 5
• +1
1 Solution

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

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

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

Commented:
This formula will do it:

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

Commented:
Copy down so each row has the right row reference
0

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

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

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

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

Hope this helps!

Dave
0

Author 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

0

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

Commented:
#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 Commented:
I have Excel 2003, I will try your process tomorrow morning.
0

Commented:
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 Commented:
Perfect, exactly what I want.
Thanks a lot.
0

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.