Excel VBA String to Number: Safe Conversion Methods

In Excel VBA, convert text that represents a number with a conversion function such as CDbl for decimals or CLng for whole numbers. For example, assign CDbl(textValue) to a variable declared As Double; choose the function to match the range and precision you need, and validate uncertain input before converting it.

Start with the number type you need

VBA does not have one best conversion function for every string. The right choice depends on whether the result should be an integer, a decimal, or a value with a fixed number of decimal places. A conversion can also fail if the text is not a valid number for the current regional settings, or if the result is outside the destination type’s range.

Whole numbers: Use CLng for a typical integer result. Use CInt only when its smaller range is sufficient.
General decimals: Use CDbl for a Double, which supports a wide range of decimal values.
Single-precision decimals: Use CSng when its lower precision and smaller range are acceptable.
Fixed four-decimal values: Consider CCur for Currency values, such as amounts that need four decimal places.
More decimal precision: CDec returns a Variant whose subtype is Decimal; VBA does not provide a separately declared Decimal variable type.

For instance, the text value 18.75 should normally be converted with CDbl, not CLng. If you need to preserve leading zeros in a code such as 0042, do not convert it to a number: those zeros are part of the identifier’s text representation, not its numeric value.

How VBA conversion functions behave

Conversion functions do more than change a variable’s label. They interpret the supplied value, produce a value of a particular type, and can report an error when the input cannot be interpreted or the result is out of range. Assigning the result to a variable declared As Double or As Long makes the intended type clear to anyone reading the code.

One important detail is rounding. CInt and CLng round a fractional value to the nearest integer rather than simply cutting off its decimal portion. At an exact half, VBA uses round-to-even behavior: 2.5 converts to 2, while 3.5 converts to 4. If your requirement is to discard the fractional part, use Fix; it truncates toward zero. Int instead rounds down to the next lower integer, so Int(-1.8) is -2 while Fix(-1.8) is -1.

Range matters too. CInt supports values from -32,768 through 32,767. CLng supports values from -2,147,483,648 through 2,147,483,647. Exceeding a type’s range raises an overflow error. CDbl has a much wider range, but binary floating-point values cannot represent every decimal fraction exactly. For most ordinary spreadsheet measurements and calculations that is suitable; for values where fixed decimal precision matters, consider Currency or Decimal instead.

Convert a value safely in VBA

For a known-good string, conversion can be as simple as assigning the result to a typed variable. This example assumes textValue contains a valid decimal string in the format recognized by the computer’s regional settings:

Sub ConvertKnownTextToDouble()
    Dim textValue As String
    Dim amount As Double

    ' Replace this sample with the text you want to convert.
    textValue = 18.75
    amount = CDbl(textValue)
End Sub

The sample string is assigned without quotation marks because VBA would otherwise treat it as numeric input at assignment time; in actual code where the source is text, it may come from a cell or a text variable. To read a worksheet cell, for example, declare a Variant for the cell’s value and pass it to the conversion function after checking it. The relevant distinction is that a String variable holds characters, while a Double variable holds a numeric value that VBA can use in arithmetic.

For a range of selected worksheet cells, this macro converts numeric constants and skips blank cells, errors, and formulas. Select only the intended text cells before running it. It changes cell contents, so save a copy of the workbook first: macros cannot be undone with Ctrl+Z.

Sub ConvertSelectedTextToNumbers()
    Dim target As Range
    Dim cell As Range
    Dim rawValue As Variant

    ' Work only on the currently selected range.
    Set target = Selection

    For Each cell In target.Cells
        rawValue = cell.Value2
        If Not cell.HasFormula Then
            If Not IsError(rawValue) Then
                If Len(Trim$(CStr(rawValue))) > 0 Then
                    If IsNumeric(rawValue) Then
                        cell.Value2 = CDbl(rawValue)
                    End If
                End If
            End If
        End If
    Next cell
End Sub

The macro uses Value2 to read and write cell values. Excel dates are stored as serial numbers, so a date cell can also have a numeric underlying value; do not include date cells unless that is intended. The macro skips formulas to avoid replacing them with their current calculated results. It converts values to Double, so use a different conversion function if you specifically need an integer or Currency value. Cell formatting is not the same as the underlying value: a cell formatted to display two decimal places may still hold more precision.

Choose between Val and conversion functions

Val is sometimes used to extract a numeric prefix from text, but it is not a drop-in replacement for CDbl or CLng. It reads from the start of a string and stops at the first character it does not recognize as part of a number. Consequently, Val applied to text such as 12.5kg returns 12.5, while a conversion function generally rejects the whole string as invalid input.

That prefix behavior can be useful when the input format deliberately puts a number first, but it can silently hide bad data. Another trap is decimal and grouping punctuation: Val recognizes a period as its decimal separator and does not interpret a comma as a thousands separator. For example, Val applied to 1,234 stops at the comma and returns 1, rather than treating the string as one thousand two hundred thirty-four. It also returns zero when it cannot find a numeric prefix, making invalid text hard to distinguish from a genuine zero.

Use CDbl, CLng, or another typed conversion when the entire input is expected to be a number and you want invalid text to be exposed. Use Val only when its prefix-parsing rules match the input you actually receive.

Handle regional settings and formatted text

VBA’s typed conversion functions interpret decimal and thousands separators according to regional conventions. A string like 1,25 may represent one and a quarter in a locale that uses a comma decimal separator, but not in a locale that expects a period. The same issue applies to grouping separators and currency symbols. A string copied from a website, CSV export, or another computer may therefore behave differently from text entered under the current Windows regional settings.

For dependable imports, decide what format the source uses before converting. If the source always uses a period for decimals and commas for grouping, normalize the text according to that known format, or import it with explicit settings, rather than assuming the current locale matches. Avoid replacing commas or periods blindly: in some source formats a comma is the decimal mark, and a global replacement can change the value by a factor of one hundred or more.

Currency symbols, spaces, and units can also prevent conversion. A value displayed as $1,234.50 may be an actual numeric cell with a currency number format, or it may be a literal string containing a dollar sign and punctuation. Those are different cases. If the worksheet cell is already numeric, no string-to-number conversion is needed; use its numeric value directly. If the characters are text, first determine exactly which characters and separators the source uses.

Validate input and diagnose conversion errors

When text comes from a user, a file, or a worksheet that may contain mixed content, check it before calling a conversion function. IsNumeric returns True when an expression can be evaluated as a number, and False for text that is not numeric. It is a useful first filter, but it does not decide whether the value fits a specific type: a value can pass IsNumeric and still be too large for CLng.

Empty cells, cells containing Excel errors, whitespace, and strings that represent dates deserve separate consideration. A cell read through Value2 can return an Error value, so test it with IsError before converting or applying string functions. IsDate is the relevant check when the intended result is a date; a date should not be treated as an ordinary quantity just because Excel stores dates as serial numbers.

Type mismatch (error 13): The supplied value cannot be converted as requested. Inspect the original text for labels, symbols, unexpected punctuation, or blank values.
Overflow (error 6): The value is outside the range of the selected type. Consider a wider type such as Double, if appropriate.
Unexpected rounding: Check whether you used CInt or CLng, which round, instead of Fix, which truncates toward zero.
Unexpected zero or partial result: Check whether Val stopped at punctuation or returned zero for text with no numeric prefix.

Do not use On Error Resume Next as a substitute for validation across a whole conversion routine. It can conceal failures and leave some cells unchanged while the macro continues. If you use error handling for exceptional cases, make the handling explicit and report which input could not be converted.

Convert worksheet text or change only its display

Sometimes a cell looks like a number but Excel stores it as text. That can prevent expected arithmetic, sorting, or chart behavior. VBA can convert selected values by assigning a numeric result back to the cell, as in the macro above. Another option is Excel’s built-in conversion tools, such as Text to Columns, when a one-time worksheet cleanup is more appropriate than writing a macro. The right method depends on whether the data is a recurring import, a one-off repair, or already numeric but displayed unexpectedly.

Do not confuse conversion with formatting. Applying a number format changes how an underlying number is shown; it does not reliably turn text characters into a numeric value. Conversely, formatting a numeric value as Text does not necessarily change the stored value back into a string. If a cell is already numeric and merely displays too many decimal places, adjust its number format rather than converting it again. If it is text, confirm that a conversion is needed before replacing the cell contents.

Also consider whether numeric conversion is appropriate at all. Account numbers, postal codes, product IDs, and values with meaningful leading zeros are usually better kept as text. Converting them can remove leading zeros, and long digit sequences may not be represented exactly as ordinary Excel numbers. Treat quantities and identifiers differently even if both consist only of digits.

Frequently Asked Questions

What is the simplest way to convert a string to a number in VBA?

Use the conversion function that matches the destination type: CDbl for a general decimal value, CLng for a whole number, or another appropriate typed function. If the input may be invalid, check it with IsNumeric and handle conversion errors.

Does CLng remove the decimal part?

No. CLng rounds to the nearest whole number, with exact halfway values rounded to the nearest even integer. Use Fix when you want to discard the fractional part by truncating toward zero.

Why does CDbl fail on a number copied from another country?

The text may use decimal or thousands separators that differ from the current regional settings. Check the source’s number convention and normalize or import it using that convention before conversion; do not assume a comma or period always has the same meaning.

Should I use Val or CDbl?

Use CDbl when the entire string should be a valid decimal and you want invalid input to cause a detectable failure. Use Val only when intentionally reading a numeric prefix, because it stops at unrecognized characters and can return a partial value.

Can I convert a cell that displays a number but is stored as text?

Yes. Read the cell’s value, validate it, convert it with an appropriate function, and write the numeric result back if needed. First make sure it is not an identifier with meaningful leading zeros, and remember that changing the number format alone does not necessarily convert text into a number.

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.