# How to Convert Text to Number in Excel with VBA

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

1. Reply Thank you SO much! VBA #3 worked perfectly for what I needed! Appreciate this simple guide

2. Reply 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

3. Reply Hi Everyone, How do i convert text to numbers in multiple columns. For example Column N, P. AB, CC?

• Reply 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. Advanced Excel Exercises with Solutions PDF  