# How to Convert String to Long Using VBA in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

You’ll learn how to convert Strings to Long in this VBA tutorial. This VBA tutorial comes with an Excel worksheet that contains all of the macros, data, and calculations used in the examples below. For your better understanding, we will use the data set that contains Product ID, Product Name, and Quantity. Let’s see, Excel VBA Convert String to Long in 3 simple ways. ## How to Convert String to Long Using VBA in Excel: 3 Ways

### Method 1: Change String to Long Using CLng

In our first method, we will see the use of CLng to convert String into Long. As you can see from the dataset, the values in Quantity are in Text format, which is a string. We will convert it to Long. Steps:
First, right-click on the sheet and go to View Code. • After that, copy and paste the VBA code below.

VBA Code:

``````Sub longConvert()

Range("E5").Value = CLng(Range("D5"))

Range("E6").Value = CLng(Range("D6"))

End Sub`````` • In the end, to Run the code, press the F5 or play button. This will change the String format as a Long. Through this code, we are telling Excel to convert Range D5 and D6 values into Long values in another column range E5 and E6.
As we just selected D5 and D6 to convert, that’s why only these two cells got converted. We can select as many cells as we want and convert the string into long.

### Method 2: Excel VBA Convert String to Long in Full Range

In our first method, we converted String to Long in Excel VBA manually inputting data. Now, we will see, how we can select a range and convert all strings into long easily.

Steps:

• First, right-click on the sheet and go to View Code. • After that, copy and paste the VBA code below.

VBA Code:

``````Sub String_long_Convert()

For i = 5 To 9

Cells(i, 5).Value = CLng(Cells(i, 4).Value)

Next

End Sub`````` • In the end, to Run the code, press the F5 or play button. This will change the String format as a Long. Through this code, we are telling Excel to convert range D5 to D9 values into Long values in another column range E5 to E9. By using For loop we are declaring the cell ranges first and the Next command will convert one data at a time and move to the next automatically.
As we can see, all the string values in range D5:D9 converted to Long Value.

### Method 3: User Function for Excel VBA Convert String to Long

Now, we will get familiar with the Function method to convert string to long in VBA.
Steps:

• By the same token as the above methods, go to the Visual Basic editor by right-clicking on the worksheet > Click on View Code.
• Now, write down the following code.

VBA Code:

``````Function stringToLong(myString As Variant)

stringToLong = CLng(myString)

End Function`````` • In the end, press F5 and run the code. We will see the date in a number.
• Now, go to cell E5 and type the following formula =stringToLong, and we will see our created function like the following image. Here, we created a user function through this code Which will help us to convert string to long and will work like any other function in Excel.

• Now, type the following
`=stringToLong(D5)` • Finally, press ENTER key and we will get our desired result. Read More: How to Convert Green Triangle to Number in Excel

## Conclusion

That’s all for the article. We have shown you several ways to convert a string into a long using VBA. Hope this has been of use to you. Please let us know if you have any questions, recommendations, or criticism in the comments area. Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

1. Reply Pete Bagalayos Apr 5, 2023 at 4:23 AM

Do the change persist? If I have a variable that was converted and then called by another function. Does it revert to its original type (string or long) or does it remain what I converted it to?

• Reply Naimul Hasan Arif Apr 5, 2023 at 10:26 AM

Thank you PETE BAGALAYOS for your comment.
Yes, the change persists. Once you convert a variable from string to long, it remains in the long format. Well, if you need the string format, you will have to convert it from long to string again. Advanced Excel Exercises with Solutions PDF  