How to Convert String to Long in VBA (With Examples)

You can use the CLng function in VBA to convert a text string to a long data type.

Here are two common ways to use this function in practice:

Method 1: Convert String to Long in VBA

Sub ConvertStringToLong()

    Dim i As Integer

    For i = 2 To 11
        Range("B" & i) = CLng(Range("A" & i))
    Next i

End Sub

This particular macro will convert each string in the range A2:A11 to a long data type and display the long values in the range B2:B11.

Method 2: Convert String to Long in VBA (Only if String is a Number)

Sub ConvertStringToLong()

    Dim i As Integer

    For i = 2 To 11
        If IsNumeric(Range("A" & i)) Then
            Range("B" & i) = CLng(Range("A" & i))
        Else
            Range("B" & i) = 0
        End If
    Next i

End Sub

This particular macro will convert each string in the range A2:A11 to a long data type only if the string is a number. Otherwise, the string will be converted to a value of zero.

The following examples show how to use each method in practice.

Example 1: Convert String to Long in VBA

Suppose we have the following column of values in Excel that are currently formatted as text strings:

Suppose we would like to convert each string to a long data type and display it in column B.

We can create the following macro to do so:

Sub ConvertStringToLong()

    Dim i As Integer

    For i = 2 To 11
        Range("B" & i) = CLng(Range("A" & i))
    Next i

End Sub

When we run this macro, we receive the following output:

Notice that each text string in column A has been converted to a long data type and is displayed in column B.

Example 2: Convert String to Long in VBA (Only if String is a Number)

Suppose we have the following column of values in Excel that are currently formatted as text strings:

Suppose we would like to convert each string to a long data type only if the string is a number and display them in column B.

We can create the following macro to do so:

Sub ConvertStringToLong()

    Dim i As Integer

    For i = 2 To 11
        If IsNumeric(Range("A" & i)) Then
            Range("B" & i) = CLng(Range("A" & i))
        Else
            Range("B" & i) = 0
        End If
    Next i

End Sub

When we run this macro, we receive the following output:

Notice that only the text strings in column A that are numbers are converted to long data types in column B.

Otherwise, the text strings are simply converted to a value of zero.

Note: You can find the complete documentation for the VBA CLng function here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Remove Spaces from String
VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String