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.

**Table of Contents**Expand

## 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 **l**ong 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.**

**Read More:** How to Convert String to Long Using VBA in Excel

**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.

**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.