How to Convert Text to Number with Excel VBA (3 Examples with Macros)

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.


Download Practice Workbook

Download this practice workbook


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.

dataset for convert text to number using VBA in excel

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.

click any cell to check number or text in excel

Now, hover the mouse cursor over the box. After that, you will see this message.

the values in are in text form instead of number

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.


3 VBA Codes to Convert Text to Number in Excel

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

1. First, press ALT+F11 on your keyboard to open the VBA editor.

2. Click on Insert > Module.

insert module for VBA editor

3. Then, type the following code:

Sub ConvertTextToNumber()
With Range("B5:B14")
.NumberFormat = "General"
.Value = .Value
End With
End Sub

4. Save the file.

5. Then, press ALT+F8. It will open the Macro dialog box.

macro dialog box to convert text to number using vba in excel

6. Select ConvertTextToNumber and Click on Run.

convert text to number in excel using vba

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

1. First, press ALT+F11 on your keyboard to open the VBA editor.

2. Click on Insert > Module.

insert module for VBA editor

3. Then, 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

4. Save the file.

5. Then, press ALT+F8. It will open the Macro dialog box.

vba macro dialog box to run vba code

6. Select ConvertUsingLoop and Click on Run.

text converted to number in excel

As you can see, we have converted our text to numbers using this VBA code.


Similar Readings


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

1. First, press ALT+F11 on your keyboard to open the VBA editor.

2. Click on Insert > Module.

insert module for VBA editor

3. Then, 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

4. Save the file.

5. Then, press ALT+F8. It will open the Macro dialog box.

macro dialog box for vba codes

6. Then select ConvertDynamicRanges and Click on Run.

text converted to number in excel

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. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.


Related Articles

Shanto

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.

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

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

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

Leave a reply

ExcelDemy
Logo