Excel haven’t provided direct feature to convert KB to MB and vice versa. Hence, here we will introduce two methods to solve this problem.
In an Excel worksheet, you will input numbers with certain units. However, some units cannot participate in calculation, such as the byte unit. For example, you can see the numbers in the image below.
There are several numbers with the unit “KB” in this column. On the other hand, it is impossible to directly use a formula to calculate the numbers with units. When you input the formula “=C14/1024” into cell D14, the result will be “#VALUE”. Besides, if there are many cells with KB, you will find it time-consuming if you convert those numbers one by one.
Hence, in order to convert KB to MB or convert MB to KB quickly, now you can use the two methods below.
Method 1: Use Excel Functions
Here we will show you the steps of using Excel functions to finish this task.
- Click cell D2 in this worksheet.
- Now input the following formula into the cell:
=IF(ISNUMBER(FIND(“KB”,C2)),ROUND(LEFT(C2,LEN(C2)-2)/1024,2) & “MB”,””)
In this formula, we will first judge whether the unit in the cell is “KB” or “MB”. If the unit is “KB”, the formula will remove the units for calculation. In the calculation, we will also use the ROUND function to round the result. You can set the criterion according to your actual need. When the calculation is finished, the new unit “MB” will be added to the number. Therefore, this formula is not so complex as it seems to be.
- And then press the button “Enter” on the keyboard. Until now, you will see nothing in cell D2.
- After that, double click the fill handle of cell D2 to fill the whole column. In this column, you will see that if the original unit is “KB”, there will be the new contents in corresponding cells. Besides, if the unit is “MB”, there will be empty value in cells.
- Now you can copy the result into the original cells. And remember to paste only values for those cells.
The next time if you need to use this formula, you can simply change the cell reference in this formula.
On the other hand, if you need to convert “MB” to “KB”, you only need to make a little adjustment. And the new formula will be like this:
=IF(ISNUMBER(FIND(“MB”,C2)),ROUND(LEFT(C2,LEN(C2)-2)*1024,0) & “KB”,””)
You can also apply this formula into your worksheet.
Method 2: Use VBA Macros
In using formula in a worksheet, you still need to copy and paste the result. And the formula is a little difficult if you are not familiar with the rules. Hence, here you can also use VBA macros in Excel to quickly finish this task.
- Click the tab “Developer” in the ribbon.
- And then click the button “Visual Basic” in the toolbar. After that, you will see the Visual Basic editor. In addition, you can also press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
- Right click in the VBA Projector area.
- And then move your cursor on the option “Insert”.
- Next choose the option “Module” in the sub menu. Therefore, there is a new module in the Visual Basic editor.
- Now in this step, input the following VBA codes into the new module:
Sub KB_To_MB()
Dim cel As Range
For Each cel In Range("C2:C28")
If cel Like "*" & "KB" Then
cel.Value = Application.WorksheetFunction.Round(Left(cel, Len(cel) - 2) / 1024, 2) & "MB"
End If
Next cel
End Sub
In this VBA macro, we will check whether the cell contains the character “KB”. And then the procedure will make the calculation. In addition, the result will be in the original worksheet. In your actual worksheet, you can also change the range according to your actual need. And if you need to input the result in other cells, you can also modify it.
- Now click the button “Run Sub” in the toolbar or press the key “F5” on the keyboard to run this macro. And next you can come back to the worksheet and check the result.
You will find that the new results have already appeared in the original cells. What’s more, you don’t need to copy the result into those cells again.
On the other hand, now if you need to convert MB to KB, you can use the macros below:
Sub MB_To_KB()
Dim cel As Range
For Each cel In Range("C2:C28")
If cel Like "*" & "MB" Then
cel.Value = Application.WorksheetFunction.Round(Left(cel, Len(cel) - 2) * 1024, 0) & "KB"
End If
Next cel
End Sub
You need to change certain elements in this macro according to your actual need. After you run this macro, you will immediately get the result in the worksheet.
A Comparison between the Two Methods
For both of the two methods, we have listed the advantages and disadvantages in the table below.
|
Comparison |
Use Excel Functions |
Use VBA Macros |
|
Advantages |
1. If you are not familiar with VBA, you can use functions to finish this task.
2. When you need to convert other units, you can also modify this formula according to your need. |
1. With just one click, you can get the result.
2. You can also modify the codes according to your need to fulfill more tasks. |
|
Disadvantages |
1. There are more steps in this step because you still need to copy the results into certain cells.
2. You will easily make mistakes in some details in this long formula. |
1. If you are not familiar with Excel VBA, you will probably meet with errors when you run macros.
2. Using VBA in Excel will always make things more complicated. |
Now you have a comprehensive understanding of the two methods. And you can use either of them to solve your problem.
Pick the Right Method of Data Recovery
Whenever you meet with Excel corruption, the first thing you need to do is trying to recovery your files. However, if you know nothing about data recovery, do not try some special methods by yourself. That’s because some misbehavior will cause further damage to your files. What’s more, you will even lose your precious data and information forever. Therefore, you need to pick the right method. And now, you can also use the third-party tool to repair Excel xlsx problems and other errors. This tool is equipped with the best technology in the world, and the error in Excel is a piece of cake to this software.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word data and outlook repair software products. For more information visit www.datanumen.com
When some one searches for his essential thing, so he/she needs to be available that in detail, thus that thing is maintained over here.