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

Get FREE Advanced Excel Exercises with Solutions!

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.

## 3 Ways to Convert String to Number in Excel VBA

### 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 datatype 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 = CSng(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 fixedpoint 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.  #### Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  