VBA convert string to integer

In order to convert a string to integer in VBA, first, we need to check whether the string can be converted. If not it will return a different string.

In order to check whether the string is a numerical value, we will use the ISNUMERIC Function. Then we will use Cint to convert the string to an integer.

Let’s take a look at the following example. You have a list of values. There are numerical values, numbers formatted as strings, blank cells, and non-numerical strings.

This example shows, in column B how the values are converted. In order to insert function, press Alt + F11. A new window will appear. Right-click on the VBA project and insert a new module.

Inside this module insert the following function.

Function ConvertString(myString)
    Dim finalNumber As Variant
    If IsNumeric(myString) Then
        If IsEmpty(myString) Then
            finalNumber = "-"
        Else
            finalNumber = CInt(myString)
        End If
    Else
        finalNumber = "-"
    End If
    
    ConvertString = finalNumber
End Function

Now, you can insert this function into an Excel worksheet.

Code explanation

3. The function checks whether the value is a numeric value,
4. An empty cell is considered a numeric value, so it will return 0. There is second if there because we want to return a dash instead of 0,
7. If the value is not numeric, convert a string to an integer.
13. The value is returned and displayed inside a cell.

Convert string to int for a selected range

This code is a bit different than the last one. This time we will use a Sub, instead of function. It works the way that you select cells you want to convert and execute the code.

This is the example.

Insert this code, select cells you want to convert and run the code.

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

Posted in vba