In Microsoft Excel, working with numbers is most common. Sometimes, we have to process a dataset with large amounts of numbers. But, you may find yourself in a situation where your numbers look like text. For this reason, you can not add, subtract, divide, multiply or perform any kind of operation. It always shows an error when you try to do this. In this tutorial, we will teach you how to convert text to number in Excel using VBA codes with suitable examples and proper illustrations.
How to Identify Numbers Formatted as Text
Microsoft Excel is smart enough to understand the difference between Text and Number. It automatically converts them to their respective formats. But, sometimes it cannot do that because of some misuse and misinterpretations of the dataset. For that reason, it keeps that numbers as texts in your workbook.
Take a look at this dataset. Here, we have some numbers in a column.
Though we have numbers in the column, they all are left-aligned like the text. Now, click any cell. You will find this box beside the cell.
Now, hover the mouse cursor over the box. After that, you will see this message.
It shows that the cell is formatted as text. In this way, you can be sure whether the cells are formatted as text or not.
How to Convert Text to Number in Excel with VBA: 3 Examples
Though you can easily convert text to numbers manually, this tutorial is all about converting text to number using VBA codes. We recommend you learn and apply all these methods to your dataset. Surely, it will come in handy in a lot of situations.
1. VBA Code with Range.NumberFormat Method to Convert Text to Number in Excel
This method is pretty simple and easy to use on any dataset. All you have to do is select the range of cells and insert it into the VBA code.
📌 Steps
- Firstly, press ALT+F11 on your keyboard to open the VBA editor.
- Secondly, click on Insert > Module.
- Then, type the following code:
Sub ConvertTextToNumber()
With Range("B5:B14")
.NumberFormat = "General"
.Value = .Value
End With
End Sub- After that, save the file.
- Afterward, press ALT+F8. It will open the Macro dialog box.
In the code,
Firstly, we created a subprocedures named ConvertTextToNumber
Then, With Range("B5:B14") this part selects the range B5:B15.
Afterward, .NumberFormat = "General" this part changes the number format to General.
Finally, .Value = .Value this part keeps the value intake.
- Next, select ConvertTextToNumber and Click on Run.
- In the end, this code will convert our text to numbers.
Read more: How to Convert String to Number in Excel VBA
2. VBA Code with Loop and CSng to Convert the Text to Number
In this method, we are using Loop and CSng functions. The CSng function basically takes any text as an argument and converts it to a single number. Our loop will go through each and every cell of the selected column. After that, we will pass each cell’s value to CSng function to convert it from text to number.
📌 Steps
- Firstly, press ALT+F11 on your keyboard to open the VBA editor.
- Then, Click on Insert > Module.
- Next, type the following code:
Sub ConvertUsingLoop()
For Each r In Sheets("Loop&CSng").UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then
r.Value = CSng(r.Value)
r.NumberFormat = "General"
End If
Next
End Sub- Afterward, save the file.
- Then, press ALT+F8. It will open the Macro dialog box.
In the code,
Firstly, we created a subprocedures ConvertUsingLoop.
Then,
For Each r In Sheets("Loop&CSng").UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then
r.Value = CSng(r.Value)
r.NumberFormat = "General"
this loop changes the format selected range into General format.
- Further, select ConvertUsingLoop and Click on Run.
As you can see, we have converted our text to numbers using this VBA code.
3. Convert Text to Numbers for Dynamic Ranges in Excel
Now, previous methods were for the selected ranges. That means you have to manually input your range of cells in the code. But sometimes your dataset might be large. In those cases, you have to remember the range of cells. This method will overcome that problem. We know our dataset starts from Cell B5. But we don’t know where it might finish.
So we dynamically identify the last used excel row that has data in it using Cells(Rows.Count, "B").End(xlUp).Row. It returns the last non-empty row number that we are concatenating with “B5:B“.
📌 Steps
- Firstly, press ALT+F11 on your keyboard to open the VBA editor.
- Then, click on Insert > Module.
- After that, type the following code:
Sub ConvertDynamicRanges()
With Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row)
.NumberFormat = "General"
.Value = .Value
End With
End Sub- Now, save the file.
- Then, press ALT+F8. It will open the Macro dialog box.
In the code,
Firstly, we created a subprocedures ConvertDynamicRanges.
Then, With Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row) this part counts the cells in column B those have values.
Afterward, .NumberFormat = "General" this part changes those cell’s format into General format.
And, .Value = .Value this part keeps the value constant.
- Now, select ConvertDynamicRanges and Click on Run.
As you can see, we are successful in converting text to numbers using the VBA codes.
💬 Things to Remember
✎ Here, we are using column B for our dataset. If your data is in a different column, change the range of cells in VBA codes accordingly.
✎ The VBA codes will work only on the active sheet.
Download Practice Workbook
Download this practice workbook
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about converting text to number in Excel using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Thank you SO much! VBA #3 worked perfectly for what I needed! Appreciate this simple guide
Hi, everywhere I look, I see the same solution… but it’s not working for me… Is it not working on 2016?
With Range(“B5:B” & Cells(Rows.Count, “B”).End(xlUp).Row)
.NumberFormat = “General”
.Value = .Value
End With
The code stop at .value = .value
Thank you for your time
Hi Everyone, How do i convert text to numbers in multiple columns. For example Column N, P. AB, CC?
Thank you in advance
Hello Polina. Thanks for asking. Basically, you have to change the cell references here.
Suppose you have data in the following ranges:
1. N3:N13
2. P1:P19
3. AB13:AB20
4. CC10:CC20
Just use the following code:
Sub ConvertTextToNumber()
With Range(“N3:N13”)
.NumberFormat = “General”
.Value = .Value
End With
With Range(“P1:P19”)
.NumberFormat = “General”
.Value = .Value
End With
With Range(“AB13:AB20”)
.NumberFormat = “General”
.Value = .Value
End With
With Range(“CC10:CC20”)
.NumberFormat = “General”
.Value = .Value
End With
End Sub
There are various ways. But this is the simplest.