VBA Convert String to Number: Functions and Examples

To convert numeric text to a number in VBA, use a conversion function such as CDbl for decimal values, CLng for whole numbers, or CInt for smaller whole numbers. Check the input first with IsNumeric when it may contain invalid text; unlike these conversion functions, Val can accept only the numeric prefix of a string.

What converting a string to a number means

A string is text, even when it looks like a number. For example, the text value "42.5" and the numeric value 42.5 may look alike in a worksheet, but VBA treats them as different types. Converting the text gives VBA a numeric value it can use in arithmetic, comparisons, calculations, or numeric sorting.

In VBA, a variable declared As String cannot hold a numeric value as a number; it holds characters. Assigning a converted value to a variable declared As Double, for example, gives you a numeric variable. A worksheet cell can also contain a number saved as text, so the visible appearance alone does not prove the underlying value is numeric.

There is no single best conversion function for every input. The right choice depends on whether the result should allow decimals, how large it may be, and whether the text follows the computer’s regional number conventions. A conversion also does not automatically make unsuitable text valid: inputs such as "12 apples" should usually be rejected rather than silently treated as 12.

Which VBA conversion function should you use?

Choose the function based on the result you need. VBA raises a runtime error if the text cannot be converted or the result is outside the target type’s range, so choosing the type is part of validating the input.

CDbl converts to a Double, a floating-point number suitable for most ordinary calculations that may include decimals.
CLng converts to a Long, a whole-number type. It rounds a fractional input rather than simply cutting off the decimal part.
CInt converts to an Integer, which has a smaller range than Long. It also rounds fractional input.
CCur converts to Currency, a fixed-point type with four decimal places, often useful when a calculation needs that scale of decimal precision.
CDec converts to the Decimal subtype of a Variant. VBA does not let you declare a variable directly As Decimal, so the result must be held in a Variant.
Val reads a numeric prefix and returns a Double, but does not require the whole string to be numeric. Use it only when that prefix-parsing behavior is wanted.

Double is a common default for general numeric input, but it is not an exact decimal representation for every value. For applications requiring exact decimal arithmetic, choose a suitable type deliberately and understand its limits. Currency is fixed to four decimal places; Decimal offers a wider decimal precision within its supported range, but is held as a Variant subtype.

Basic conversion examples

Each conversion function can be called directly with a string. The following VBA procedure demonstrates the common types. The examples use text that is valid under a regional setting where a period is the decimal separator.

Sub ShowNumberConversions()
    Dim decimalValue As Double
    Dim wholeValue As Long
    Dim currencyValue As Currency
    Dim decimalVariant As Variant

    ' Convert numeric text to the chosen VBA types.
    decimalValue = CDbl("18.75")
    wholeValue = CLng("19.2")
    currencyValue = CCur("18.7500")
    decimalVariant = CDec("18.75")
End Sub

In this example, decimalValue is 18.75, wholeValue is 19, and currencyValue represents 18.7500. The Decimal value is stored in a Variant. These functions convert the supplied value; they do not change the original string variable.

For instance, with Dim sourceText As String and Dim amount As Double, assigning amount = CDbl(sourceText) converts the text for the assignment. The source remains a String. Avoid assigning the result back to a String if you need it to remain numeric, since that would store text again.

Validate text before converting

Use IsNumeric to check whether an expression can be evaluated as a number before calling a conversion function. It returns True or False; it does not perform the conversion or guarantee that the value fits in a narrower type such as Integer. A value can pass the numeric check and still be too large for CInt or CLng.

For example, a worksheet macro can read a value from A2, convert valid numeric content into a Double, and write the result to B2. It sends blank or nonnumeric input to B2 as a clear message instead of stopping on a conversion error.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, so a backup gives you a way to recover if a macro changes cells unexpectedly.

Sub ConvertA2ToB2()
    Dim sourceCell As Range
    Dim rawValue As Variant

    ' Read the source without changing its original cell.
    Set sourceCell = ActiveSheet.Range("A2")
    rawValue = sourceCell.Value2

    If IsError(rawValue) Then
        ActiveSheet.Range("B2").Value = "Cell contains an error"
    ElseIf Len(Trim$(CStr(rawValue))) = 0 Then
        ActiveSheet.Range("B2").Value = "Blank input"
    ElseIf IsNumeric(rawValue) Then
        ActiveSheet.Range("B2").Value = CDbl(rawValue)
    Else
        ActiveSheet.Range("B2").Value = "Invalid number"
    End If
End Sub

The macro checks for a cell error before converting the value to a String for the blank check. That order matters because an Excel error value is not ordinary text. If you adapt the procedure to process a full column, decide what should happen to blank, invalid, and error cells, and test on a copy before applying it to important data.

How rounding and truncation differ

CInt and CLng round fractional values to the nearest whole number. VBA uses banker’s rounding for exact halfway cases: the result goes to the nearest even integer. For example, CInt(2.5) gives 2, while CInt(3.5) gives 4. This can surprise someone expecting every .5 value to round away from zero.

If you need to remove the fractional part instead of rounding, use Fix or Int, not CInt or CLng. Fix truncates toward zero: Fix(19.7) is 19 and Fix(-19.7) is -19. Int rounds down to the next lower integer, so Int(-19.7) is -20. These are distinct operations, particularly for negative values.

Also keep the target type’s range in mind. An Integer is limited to -32,768 through 32,767, while a Long supports a much larger whole-number range. If input may exceed those limits, a conversion to Integer or Long can fail even when IsNumeric returns True. Use a type suited to the expected values.

Locale, decimal separators, and Val

Conversion functions such as CDbl interpret numeric text according to regional settings. Decimal and thousands separators vary between locales, so text that looks unambiguous to one computer may be interpreted differently or rejected on another. If your data comes from a file or external system, identify its number format and normalize it deliberately before conversion.

Val behaves differently: it recognizes a period as its decimal separator regardless of regional settings and stops reading at the first character it does not recognize. For example, Val("45kg") returns 45. That can be useful for intentionally extracting a numeric prefix, but it is dangerous as a general validity check: it can turn malformed input into a plausible-looking number. It also does not treat a comma as a decimal point, so Val("1,25") returns 1.

Do not substitute Val for CDbl merely because one conversion fails. First establish whether the source uses a decimal comma, a thousands separator, a currency symbol, or extra text. Then parse according to that known format. If users enter values on different computers, documenting the accepted format can prevent inconsistent results.

Converting numbers stored as text in worksheet cells

A cell that displays digits may still contain text. One way to check a value in VBA is to inspect the Variant returned by the cell, for example with VarType, or simply test whether the value can be used numerically. Range.Value2 is often convenient for reading cell contents because it returns the underlying value without the Currency and Date subtypes used by Range.Value.

After converting the text, write the numeric result to a cell rather than formatting the original text to look like a number. Number formatting controls how a value appears; it does not, by itself, turn text into a numeric value. A value written as a number can then participate in arithmetic and numeric sorting. For a batch conversion, using a separate output column first makes it easier to compare results and find records that need correction before replacing the source data.

Be cautious with identifiers such as postal codes, product codes, and account numbers. They may contain only digits but are not quantities. Converting them to numbers can remove leading zeros or change how long values display. Keep identifiers as text unless calculations are genuinely required.

Common conversion errors and how to avoid them

A conversion error usually indicates invalid text, a value outside the destination type’s range, or an unexpected value such as an Excel error. A blank cell, a string containing spaces, and the word Null are not interchangeable cases. Handle blanks and errors explicitly when reading worksheet data, and decide whether to skip, flag, or reject each one.

Type mismatch: Check that the input is not an error value or other unsupported expression, then validate before converting.
Overflow: Use a wider or more appropriate numeric type, or reject values outside the range your application allows.
Unexpected rounding: Use CDbl when decimals must be preserved, or choose Fix or Int if truncation is specifically required.
Wrong decimal interpretation: Confirm the source’s separators and regional settings; do not assume all machines parse the same text identically.
Text that partly looks numeric: Prefer a whole-value check and conversion over Val when trailing characters should make the input invalid.

Error handling with On Error can be useful in a larger procedure, but it should not replace input checks. If every conversion error is silently ignored, bad data may go unnoticed. A clear status column or a message identifying the source cell is often more helpful than leaving an unexplained zero or blank.

Frequently Asked Questions

How do I convert a string to a number in VBA?
Use a conversion function such as CDbl(myString) for a decimal-capable Double or CLng(myString) for a whole number. Check uncertain input with IsNumeric first, and choose a type that can hold the result.

What is the difference between Val and CDbl in VBA?
CDbl converts a complete numeric expression using regional number conventions and raises an error when conversion is not possible. Val reads a numeric prefix, uses a period for decimals, and stops at an unrecognized character, so it may accept text that should be rejected.

Does CInt round or truncate?
CInt rounds to the nearest Integer, including banker’s rounding for halfway values. It does not simply remove digits after the decimal point. Use Fix for truncation toward zero.

Why does CDbl fail on a number with a comma?
The comma may be a decimal separator or a thousands separator, depending on the source format and regional settings. Check what the text represents and parse or normalize it using an explicit, consistent convention before converting.

Can IsNumeric confirm that a value will fit in a Long?
No. IsNumeric checks whether an expression is numeric, not whether it fits the range of a particular VBA type. A value may pass that check and still overflow when assigned to a Long; validate the allowed range as well.

Choosing a safe approach

For ordinary numeric text, validate the whole input and convert with the function that matches the intended result: commonly CDbl for decimal calculations and CLng for whole numbers. Use CInt only when its smaller range and rounding behavior are appropriate. Reserve Val for cases where reading a numeric prefix is an intentional requirement.

For spreadsheet data, check for cell errors and blanks, preserve the original column until converted results are verified, and treat regional number formats as part of the input specification. Those habits prevent many conversion failures and, just as importantly, avoid turning bad text or identifiers into misleading numbers.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.