How to Identify Numbers Formatted as Text
- Take a look at this dataset. We have some numbers in a column.
- Though we have numbers in the column, they all are left-aligned like the text.
- Click any cell. You will find a warning box.
- Hover over the box. You will see this message.
- It shows that the cell is formatted as text.
How to Convert Text to Number in Excel with VBA: 3 Examples
Method 1 – VBA Code with the Range.NumberFormat Method to Convert Text to Number in Excel
Steps
- Press Alt + F11 to open the VBA editor.
- Click on Insert, then on Module.
- Insert the following code:
Sub ConvertTextToNumber()
With Range("B5:B14")
.NumberFormat = "General"
.Value = .Value
End With
End Sub- Save the file.
- 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.
- Select ConvertTextToNumber and click on Run.
- This code will convert our text to numbers.
Read More: How to Convert String to Number in Excel VBA
Method 2 – VBA Code with a Loop and CSng to Convert Text to Number
Steps
- Press Alt + F11 to open the VBA editor.
- Click on Insert, then on Module.
- Copy 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- Save the file.
- 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.
- Select ConvertUsingLoop and click on Run.
Method 3 – Convert Text to Numbers for Dynamic Ranges in Excel
Steps
- Press Alt + F11 on your keyboard to open the VBA editor.
- Click on Insert and then on Module.
- Insert the following code:
Sub ConvertDynamicRanges()
With Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row)
.NumberFormat = "General"
.Value = .Value
End With
End Sub- Save the file.
- 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.
- Select ConvertDynamicRanges and click on Run.
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 the Practice Workbook
Related Articles
- How to Convert Date from String Using VBA
- How to Convert String to Double in Excel VBA
- How to Convert Text to Date with Excel VBA
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.