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

In this article, we’ll discuss several methods to convert string to number in Excel VBA. We’ll see how to use the built-in functions and create a custom function using them to convert string to number with VBA code examples.

How to Convert String to Number in Excel VBA: 3 Methods

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.

1.1 String to Integer

To convert string to integer, we can use the CInt function in our code. The CInt function takes only one argument and that should be a numeric value. Let’s try 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.

1.2 String to Long

The CLng function converts a numeric string value to a long datatype. It works similarly to the CInt function. The key difference lies in its range which is between -2,147,483,648 and 2,147,483,647.

The code to run is here below:
Sub StringToNumber()
For i = 3 To 9
Cells(i, 3).Value = CLng(Cells(i, 2))
Next
End Sub

Here, cells B3:B9 contain some numerical string value, and converted long numbers are in cells C3:C9. The CLng function converted -32800 and 32800 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.

1.3 String to Decimal

Using the CDec function we can convert a numerical string value to a decimal datatype. 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

1.4 String to Single

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. But it’ll also get an error if the input numeric value is out of range.

1.5 String to Double

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

The double datatype ranges- (i) -1.79769313486231E308 to -4.94065645841247E-324 for negative numbers.
(ii) 4.94065645841247E-324 to 1.79769313486232E308 for positive numbers.

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. But it’ll also get an error if the input numeric value is out of range.

1.6 String to Currency

The currency data type is handy when calculations are related to money. Moreover, if we want more accuracy in fixed–point calculation, the use of the currency data type is a good choice. We need to use the CCur function to convert a string into a currency data type. The data type ranges from -922,337,203,685,477.5808 to 922,337,203,685,477.5808.

Code to convert numeric string value of cells B3:B7 to currency data type in cells C3:C7 is here below.

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

1.7 String to Byte

The CByte function converts numerical string values to the byte data type which ranges from 0 to 255.
Code is as follows:

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.

2. Use of Custom VBA Function to Check and Convert String to Number in Excel

In this illustration, we’re going to make a custom function for converting strings to numbers. We can then use this custom function in our worksheet like a built-in function. In this example, we’ll use the CInt function to convert strings to integers while creating the custom function. We could also use all the other functions described in method 1 to convert strings to different data types. Now, follow the steps below to accomplish this.
Steps:

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

Now, in the visual basic editor, copy and paste the following code and then 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.

Now, press Enter after closing the parentheses.

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

The final output is the coveted integer numbers from the string values.

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

In this illustration, we’ll convert a range of selected cells containing string values to integer numbers. If any cell contains a non-numeric value in it, the output will be a dash (-) line instead. Follow the steps:

Select cells B3:B6 having numeric strings values 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

Now, 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 Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Convert String to Number.xlsm

Conclusion

Now, we know how to convert string values to numbers in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.