Convert Range from Text to Number in VBA

Sometimes you get a dataset that has numbers stored as text. This means you cannot work with the values as you would with actual numbers.

If you try to, they give unexpected results. For example, if you sum them, you get a 0 (zero) regardless of the values involved. This tutorial shows how to convert numbers stored as text to actual numbers using Excel VBA.

How to Identify Numbers Stored as Text

Sometimes Excel fails to convert numeric values to number format and stores them as text as in the example dataset below:

The values in the dataset are left-aligned meaning Excel treats them as text values.

When you select any cell in the dataset a yellow triangle with an exclamation point pops up beside it:

Table

Description automatically generated with medium confidence

When you hover over the yellow triangle, you see the message “The number in this cell is formatted as text or preceded by an apostrophe.”

Graphical user interface, application, table, Excel

Description automatically generated

3 Methods to Convert Text to Number Using Excel VBA

There are several ways we can use to convert numbers stored as text to number format in Excel. This tutorial focuses on converting using Excel VBA. We shall use copies of the example dataset in our illustrations.

Method 1: Use the Excel VBA Range.NumberFormat Property

The Range.NumberFormat property returns or sets a Variant value that represents the format code for the object.

We use the following steps:

In the active worksheet that contains the dataset, press Alt + F11 to open the Visual Basic Editor (VBE).
Click Insert >> Module to insert a new module.

Type the following procedure in the module and remember to change the range to your range:

Sub convertTextToNumber()
    With Range("A2:A15")
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub

Sub convertTextToNumber()

With Range("A2:A15")

.NumberFormat = "General"

.Value = .Value

End With

End Sub

Save the procedure and save the workbook as a macro-enabled workbook.
Place the cursor anywhere in the procedure and press F5 to run the code.
Press Alt + F11 to switch back to the active worksheet to see the results of the procedure.

Table

Description automatically generated with low confidence

The values in the data range are now right-aligned because they have been converted to number format.

Close the Visual Basic Editor.

Explanation of the procedure

Sub convertTextToNumber()
    With Range("A2:A15")
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub

Sub convertTextToNumber()

With Range("A2:A15")

.NumberFormat = "General"

.Value = .Value

End With

End Sub

The With statement is used with the mentioned range to minimize the code.
NumberFormat = “General”. The General number format is assigned to the values in the mentioned range.
Value = Value. The values that have been converted to the General number format are assigned to the cells in the range.

Method 2: Use Excel VBA Loop and CSng function

In this method, the procedure loops through the dataset and converts the values to single-precision numbers using the CSng function.

We use the following steps:

In the active worksheet that contains the dataset press Alt + F11 to open the Visual Basic Editor.
Click Insert >> Module to insert a new module.
Type the following procedure in the new module:

Sub convertTextToNumber2()
Dim r As Variant
    For Each r In Sheets("Method 2").UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then
            r.Value = CSng(r.Value)
            r.NumberFormat = "General"
        End If
    Next
End Sub

Sub convertTextToNumber2()

Dim r As Variant

For Each r In Sheets("Method 2").UsedRange.SpecialCells(xlCellTypeConstants)

If IsNumeric(r) Then

r.Value = CSng(r.Value)

r.NumberFormat = "General"

End If

End Sub

Save the procedure and save the workbook as a macro-enabled workbook.
Place the cursor anywhere in the procedure and press F5 to run the procedure.
Press Alt + F11 to switch back to the active worksheet to see the results of the procedure.

The values in the data range are now right-aligned because they have been converted to number format.

Close the Visual Basic Editor.

Explanation of the code

Sub convertTextToNumber2()
Dim r As Variant
    For Each r In Sheets("Method 2").UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then
            r.Value = CSng(r.Value)
            r.NumberFormat = "General"
        End If
    Next
End Sub

Sub convertTextToNumber2()

Dim r As Variant

For Each r In Sheets("Method 2").UsedRange.SpecialCells(xlCellTypeConstants)

If IsNumeric(r) Then

r.Value = CSng(r.Value)

r.NumberFormat = "General"

End If

End Sub

The variable called r of type Variant is declared. The Variant data type can contain any type of data except the fixed-length String data. It is appropriate because the variable will first contain text values and then the converted numeric values.
For Each Loop goes through each cell that contains a constant (a value that is directly inserted into a cell) in the used range of the mentioned worksheet and does the following:
Checks if it is a number using the IsNumeric function. If it is a number, it turns it to single precision using the CSng function and assigns the General number format to it.

Method 3: Convert Text to Number in a Dynamic Range

In the previous methods, we worked with selected ranges.

In this method, we work with a dynamic range.

We use the following steps:

In the active worksheet that contains the dataset press Alt + F11 to open the Visual Basic Editor.
Click Insert >> Module to insert a new module.
Type the following procedure in the new module:

Sub ConvertTextToNumber3()
    With Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub

Sub ConvertTextToNumber3()

With Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

.NumberFormat = "General"

.Value = .Value

End With

End Sub

Save the procedure and save the workbook as a macro-enabled workbook.
Place the cursor anywhere in the procedure and press F5 to run the code.
Press Alt + F11 to switch back to the active workbook to see the results of the procedure.

The values in the data range are now right-aligned because they have been converted to number format.

Close the Visual Basic Editor.

Explanation of the procedure

Sub ConvertTextToNumber3()
    With Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub

Sub ConvertTextToNumber3()

With Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

.NumberFormat = "General"

.Value = .Value

End With

End Sub

The With statement is used with the mentioned range to minimize the code.
“A2:A” & Cells(Rows.Count, “A”).End(xlUp).Row. The number of the last nonblank row in column A is dynamically identified by Cells(Rows.Count, “A”).End(xlUp).Row. That number is concatenated with “A2:A” to identify the range.
NumberFormat = “General”. The number format of the range is set to General.
Value = Value. The values that are now in General number format are assigned to the cells in the range.

Conclusion

If you get a dataset that has numbers that are stored as text, you cannot work with the values as you would with numbers. For example, if you try to sum them, you get a 0 (zero) regardless of the values involved. This tutorial explained how to convert such data range from text format to number format using Excel VBA.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 1,916