Skip to content

VBA Convert Text String to Number

Associated Files Download Links

You may be required to convert numbers stored as text to actual numbers in your VBA code. In this tutorial, we are going to go over the functions that you need to use to convert a string to an integer, long, double, decimal or currency data type (Click here to learn about converting numbers to strings)

Convert String to Integer

You can use the CInt or CLng function to convert a string to an integer. If the fraction is less than .5 the function will round down, if the fraction is greater than or equal to .5 the function will round up. The following code will convert a string to an integer:

The result is:
Using the CInt Function in VBA

The following code uses the CLng function to convert a string to an integer:

The result is:

Using the CLng Function in VBA

Note: You can use the CInt or CLng function to convert a string to an integer or long (respectively) data types. The Long Data type is the same as an integer data type except larger numbers are allowed. In the past, the distinction was required because of memory constraints. In modern programming, there’s no reason not to use the long data type since memory is no longer an issue. So it’s always better to use a long data type instead of an integer.

You can use the Immediate Window to see how the value would be processed if not converted to an integer:

Using the Immediate Window

Usually, the text will be stored as a variable and this variable will need to be converted to a number data type as shown in the code below:

Convert String to Decimal

You can use the CDbl or CDec function to convert a string to a decimal. The following code would convert a string to a double data type:

The result is:

Converting a String to a Double Decimal Type

The following code would convert a string to a decimal data type:

The result is:

Converting a string to a decimal data type in VBA

You can use the Immediate Window to see how the value would be processed if not converted to a double or decimal data type:

The result is:

Using Debug.Print to Display values

Note: The decimal data type can store larger numbers than the double data type, so it’s always advisable to use the decimal data type when you are uncertain.

Convert String to Currency

You can use the CCur function to convert a string to a currency. The following code would convert a string to a currency data type:

The result is:

Converting a String to the Currency Data Type

You may also like some of this related content...

Advertisements
Automate Excel
Left Menu Icon