VBA CStr, CDate & Val in Excel — Convert Text to Numbers & Dates the Right Way

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-10

TL;DR — CStr, CDate, Val, CLng and CDbl are the conversion functions: they change a value's type — text into a real number, text into a real date, a number into text. That is the opposite of Format, which only changes how a value looks. Get the difference and you stop the single biggest source of silent VBA data bugs.

Sub ConvertDemo()
    Debug.Print CStr(1234.5)            ' "1234.5"        number  -> text
    Debug.Print CDbl("1234.5") * 2      ' 2469            text    -> number, then real math
    Debug.Print CLng("42")              ' 42              text    -> Long integer
    Debug.Print CDate("2026-04-01")     ' a real Date you can sort and subtract
    Debug.Print Val("12px")             ' 12              reads digits, stops at the first non-digit
End Sub

The mental model: conversion changes what a value IS

A cleanup tour through this cluster: Trim makes data clean, Format makes it look right, and the C… functions make it the right type. Those are three different jobs, and the cardinal sin is confusing the last two.

A cell that displays 2026-04-01 might be a real date (sortable, subtractable, filterable by month) or it might be text that merely looks like one. Format produces the second kind; CDate produces the first. When your dates won't sort or your "numbers" won't sum, you almost always have text where you needed a real typed value — and the fix is a conversion, not more formatting. Reach for CDate/CDbl/CLng to turn text into the thing it's pretending to be, and CStr to go the other way deliberately.

The one rule: CDate reads ambiguous dates by the machine's locale

This is the rule that quietly corrupts data across a team:

CDate parses a date string using the regional settings of the PC it runs on. CDate("01/02/2026") is 2 January on a US machine and 1 February on a European one. Same workbook, same code, different data — and nothing errors.

There is no warning, because both readings are valid dates. The bug only surfaces when the file moves to a colleague with different settings, or runs on a server configured for another region — and by then the wrong dates are baked into a month of reports.

' ⚠ DANGER — the result depends on whoever's PC runs it
d = CDate("01/02/2026")          ' Jan 2 in the US, Feb 1 in most of Europe

' ✓ SAFE — build the date from its parts, locale-independent
d = DateSerial(2026, 2, 1)       ' always 1 February, on every machine

The fix is to stop handing ambiguous strings to CDate at all. When you control the source, parse the parts and build the date with DateSerial(year, month, day) — it takes three numbers and can't be misread. When you must accept "01/02/2026", Split it on "/" and decide the order yourself rather than letting the locale decide for you. Treat CDate as safe only for unambiguous formats like ISO "2026-04-01".

The second trap: Val is locale-blind, CDbl is not

Both turn text into a number, but they disagree about the decimal separator, and that disagreement eats European data:

• Val always treats . as the decimal point and , as a stopper. It ignores spaces and reads left-to-right until it hits something non-numeric. Val("12px") → 12. Val("1,234.5") → 1 (the comma stops it).
• CDbl respects the locale. On a machine where the comma is the decimal separator, CDbl("3,14") → 3.14.

So the exact same string flips meaning depending on which you pick:

' User on a German PC types "3,14" meaning three-point-one-four
Debug.Print Val("3,14")     ' 3      <- comma stops it; the ",14" is silently dropped
Debug.Print CDbl("3,14")    ' 3.14   <- locale-aware, the value the user meant

The rule that keeps you safe: Val for fixed, English-format strings you control (stripping "12px" down to 12, reading a CSV you exported yourself with . decimals); CDbl/CLng for anything a user typed or that came from a localized source. Mixing them is how a column of European prices quietly loses its decimals.

The third trap: CLng rounds, and not the way you expect

CLng and CInt don't truncate — they round, using banker's rounding (round half to even):

Debug.Print CLng(2.5)       ' 2   <- rounds to the EVEN number, not up
Debug.Print CLng(3.5)       ' 4
Debug.Print Int(2.7)        ' 2   <- Int/Fix truncate instead

If you want "always round half up" or "chop the decimals," CLng is the wrong tool — use Int, Fix, or WorksheetFunction.Round. And remember CInt overflows above 32,767; reach for CLng for anything that might be a real-world count.

One more on the text side: CStr(123) gives "123", but the older Str(123) gives " 123" with a leading space reserved for the sign — and Str always uses . regardless of locale. For clean number-to-text, CStr (or Format when you want a specific look) beats Str every time.

When to use which

The opinion: never let the locale decide your data

Here's the line: implicit conversion and CDate on ambiguous strings are time bombs, not conveniences. The code that does someDate = rng.Value or CDate(textCell) and "just works" on your machine is the same code that silently produces February instead of January on the server. It passes every test you run, because you run them on your PC.

So convert deliberately and unambiguously. Build dates with DateSerial. Parse user numbers with CDbl, fixed strings with Val, and know which you're holding. When a whole column arrives as "numbers stored as text," the real fix isn't a formatting pass — it's a conversion at the import boundary, once, with the locale pinned down. The teams that get burned are the ones who trusted the implicit reading; the ones who don't are the ones who decided the format themselves.

Common conversion mistakes (and the fix)

When the conversions pile up — describe the data you want

You didn't want a tour of CDate versus DateSerial. You wanted "read this export, where the dates are dd/mm and the amounts use commas, and load it correctly into my model." By the time you've guarded IsDate, pinned the locale, chosen CDbl over Val, and rebuilt the ambiguous dates, the conversion plumbing is the macro. ExcelMaster Agent lets you describe the data instead — "the dates in column A are day-first, the amounts in B use a comma decimal; convert them to real dates and numbers" — and it writes Python that parses them unambiguously, backing up your workbook first. Try it free →

Related guides

• VBA Trim — Why It Doesn't Remove Your Spaces (and the Chr(160) Fix)
• VBA Format — Dates, Numbers & Why Formatting Breaks Your Math
• VBA InStr — Find Text Inside a String
• VBA Split — Turn One String Into an Array
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

How do I convert a string to a number in VBA? Use CDbl or CLng for input from users or localized sources, because they respect the regional decimal separator: CDbl("3,14") is 3.14 on a comma-decimal machine. Use Val only for fixed English-format strings you control, since Val always treats . as the decimal point and stops at the first non-digit.

What is the difference between CStr and Str in VBA? CStr converts a number to text cleanly and follows the locale; Str reserves a leading space for the sign (so Str(123) is " 123") and always uses . as the decimal point regardless of region. Prefer CStr for number-to-text.

Why does CDate give the wrong date in VBA? Because CDate parses ambiguous strings like "01/02/2026" using the machine's regional settings — Jan 2 in the US, Feb 1 in much of Europe. Build dates from parts with DateSerial(2026, 2, 1) to make them locale-independent, and reserve CDate for unambiguous formats like ISO "2026-04-01".

Does CLng round or truncate in VBA? CLng (and CInt) round, using banker's rounding — CLng(2.5) is 2, CLng(3.5) is 4. To truncate the decimals instead, use Int or Fix; to round half up, use WorksheetFunction.Round. Note CInt overflows above 32,767, so use CLng for larger values.

How do I avoid a Type mismatch when converting in VBA? Test the input first: If IsNumeric(s) Then n = CDbl(s) and If IsDate(s) Then d = CDate(s). These guards stop a stray non-numeric or invalid date string from raising a runtime error mid-loop.