How to Convert Text to Number in Excel with VBA

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.

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

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

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

Related Articles

• How to Convert Date from String Using VBA
• How to Convert String to Double in Excel VBA
• How to Convert String to Long Using VBA in Excel
• How to Convert Text to Date with Excel VBA