How to Convert String to Number in Excel VBA (3 Methods)

Method 1 – Convert String to Number Using Type Conversion Functions

Excel provides several built-in type conversion functions. We can use them in our VBA code to easily convert from string datatypes to different datatypes.

Case 1.1 – String to Integer with the CInt Function

Use the following code in the Visual Code Editor:

Sub StringToNumber()
MsgBox CInt(12.3)
End Sub

Press F5 to run the code. The output is shown in the MsgBox.

The CInt function converted the numeric string value (“12.3”) to an integer 12.

To understand more about the CInt function, run the following code in the code editor and observe the results.

Sub StringToNumber()
For i = 3 To 7
Cells(i, 3).Value = CInt(Cells(i, 2))
Next
End Sub

The output is here in the following screenshot.

Code Explanation

In this code, we used the For…Next loop to apply the CInt function on the strings of cells B3:B7. The outputs are printed in cells C3:C7. We used the Cells function to specify the input values and where to print the output values.

Results

The CInt function converted 25.5 to the next integer number 26. On the other hand, it converted 10.3 to 10, not 11. When a decimal numeric value is less than .5, the function rounds down to the same number. But the decimal numeric string value turns into the next integer number if it is equal to or greater than .5.

Note

The integer value has a range between -32,768 to 32,767. If we put a numeric value that is out of this range, Excel will show an error.

Case 1.2 – String to Long with the CLng Function.

The key difference here is that long uses a larger range which is between -2,147,483,648 and 2,147,483,647.

Run the following code:

Sub StringToNumber()
For i = 3 To 9
Cells(i, 3).Value = CLng(Cells(i, 2))
Next
End Sub

Cells B3:B9 contain some numerical string values, and converted long numbers are in cells C3:C9. The CLng function converted -32,800 and 32,800 successfully to long numbers, which the CInt function couldn’t. But it’ll also get an error if the input numeric value is out of range.

Case 1.3 – String to Decimal with CDec

Run the following code to convert the numerical values in cells B3:B7 to the decimal datatype.

Sub StringToNumber()
For i = 3 To 7
Cells(i, 3).Value = CDec(Cells(i, 2))
Next
End Sub

Case 1.4 – String to Single with CSng

In this example, we’ll turn the input strings into single datatype (single-precision floating-point) numbers. For this, we need to use the CSng function.

The single datatype ranges- (i) -3.402823E38 to -1.401298E-45 for negative numbers.
(ii) 1.401298E-45 to 3.402823E38 for positive numbers.

Run the following code in the visual basic editor:

Sub StringToNumber()
For i = 3 To 7
Cells(i, 3).Value = CSng(Cells(i, 2))
Next
End Sub

In the output, cells B3:B9 contain some numerical string value, and converted single datatype numbers are in cells C3:C9.

Case 1.5 String to Double with CDbl

The double datatype has an incredibly broad range, from 10^-324 to 10^324 (but isn’t able to correctly represent all values within it).

Run the following code in the visual basic editor.

Sub StringToNumber()
For i = 3 To 7
Cells(i, 3).Value = CDbl(Cells(i, 2))
Next
End Sub

In the output, cells B3:B9 contain some numerical string value and converted double datatype numbers are in cells C3:C9.

Case 1.6 String to Currency with CCur

The currency data type ranges from -922,337,203,685,477.5808 to 922,337,203,685,477.5808.

The code you can use to convert numeric string values of cells B3:B7 to the currency data type in cells C3:C7 is below:

Sub StringToNumber()
For i = 3 To 7
Cells(i, 3).Value = CCur(Cells(i, 2))
Next
End Sub

Case 1.7 String to Byte with CByte

The CByte function converts numerical string values to the byte data type which ranges from 0 to 255.

Use the following code:

Sub StringToNumber()
For i = 3 To 7
Cells(i, 3).Value = CByte(Cells(i, 2))
Next
End Sub

In the output, cells B3:B9 contain some numerical string value, and converted byte data type numbers are in cells C3:C9. But it’ll also get an error if the input numeric value is out of range.

Method 2 – Use of a Custom VBA Function to Check and Convert a String to a Number in Excel

Steps:

In cells B3:B7, we have some numerical string values.

In the visual basic editor, copy and paste the following code and press Ctrl + S to save.

Function StringToNumber(inputStr)
    Dim convertedNum As Variant
    If IsNumeric(inputStr) Then
        If IsEmpty(inputStr) Then
            convertedNum = "-"
        Else
            convertedNum = CInt(inputStr)
        End If
    Else
        convertedNum = "-"
    End If
    StringToNumber = convertedNum
End Function

In cell C3, start typing the function name (StringToNumber). Excel will automatically suggest the function to use.
Press the Tab key to enter the function.

Put the cell reference B3 as the only argument.

Press Enter after closing the parentheses.

Double-click the Fill Handle at the right bottom corner of cell C3 to apply the function to cells C4:C7.

The final output is in the following image.

Method 3 – VBA Code to Convert a Selected Range of Cells to Numbers in Excel

If any cell contains a non-numeric value in it, the output will be a dash (-) line instead.

Select cells B3:B6 and B7 which contains a non-numeric.

In the Visual Basic Editor, copy and paste the following code:

Sub StringToNumber()
    Dim convertedNum As Variant
    For Each cell In Selection
        If IsNumeric(cell) Then
            If IsEmpty(cell) Then
                convertedNum = "-"
            Else
                convertedNum = CInt(cell)
            End If
        Else
            convertedNum = "-"
        End If
        With cell
            .Value = convertedNum
            .HorizontalAlignment = xlCenter
        End With
    Next cell
End Sub

Press F5 to run the output as shown in the following screenshot.

Notes

We used the isNumeric function in the 2nd and 3rd methods in our VBA code that checks whether an expression can be converted to a number.
In method 1, we used built-in functions (CInt, CDbl, CSng…..) to convert numeric string values to numbers. But if there is a non-numeric value, it’ll show a mismatch error.

Download the Practice Workbook

Convert String to Number.xlsm