VBA Convert to String: How CStr Works

VBA & macros · Updated November 22, 2026

In VBA, use the built-in CStr function to convert a value to a string: for example, CStr(42) returns the text "42". It is the usual choice for converting numbers, dates, Boolean values, and other supported values; the important exceptions are Null, objects, and arrays, which need separate handling.

The standard VBA conversion function

CStr(expression) evaluates an expression and returns its string representation. The result is a VBA String, not a number formatted for display in an Excel cell. For ordinary values, the function is direct and readable:

  • CStr(42) produces "42".
  • CStr(True) produces "True".
  • CStr(Empty) produces a zero-length string, "".

The argument can be a literal, a variable, or an expression. For example, if amount is a numeric variable, result = CStr(amount) converts its current value. The function does not change the original variable: it returns a string that can be assigned to a string variable, combined with other text, or passed to a procedure that expects text.

Use CStr when the intent is conversion rather than presentation. If the goal is to show a date in a chosen layout or a number with a set number of decimal places, use Format or Format$ instead. Conversion and display formatting are related, but they solve different problems.

How CStr handles common data types

The representation depends on the value’s data type and, for some values, the computer’s regional settings. A Boolean becomes the words True or False. A date becomes a date-and-time string in a locale-sensitive representation. Numeric values become text using the applicable regional conventions, including the decimal separator.

That regional behavior matters when text is written to a file, exchanged with another system, or compared against a fixed string. On one computer a decimal value may be represented with a period; on another, the local decimal separator may be a comma. Similarly, the order of day, month, and year in a date string can differ. Do not assume that a plain CStr result is a universal data format.

For values held in a Variant, conversion follows the value currently stored in the Variant. This is convenient when reading mixed worksheet data, but it also means that a cell containing a date, an error, or a blank may not behave like a cell containing ordinary text. Check the type or special value before converting when the input is not controlled.

A basic VBA example

This procedure converts a number to a string and prints the result in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows.

Sub ConvertNumberToText()
    ' Convert a numeric value to a VBA String.
    Dim quantity As Long
    Dim textValue As String

    quantity = 42
    textValue = CStr(quantity)
    Debug.Print textValue
End Sub

The printed result is 42, but textValue is text rather than a numeric value. You can also use the converted value in a message, such as "Quantity: " & CStr(quantity). The ampersand joins strings; using CStr explicitly makes the conversion easy to see and avoids confusing string concatenation with arithmetic using the + operator.

Before running any macro that changes a workbook, save a separate copy first. VBA macros cannot be undone with Ctrl+Z. The example above only prints a value, but this precaution is important when adapting conversion code to edit cells or files.

Choose CStr or Format for the result you need

CStr answers, “What is the string representation of this value under the current locale?” Format answers, “How should this value be displayed according to a pattern?” For example, if a date must be written in a predictable year-month-day order, apply a format rather than relying on the computer’s short-date setting.

With a date variable named sampleDate, this example requests a specific layout. In VBA date-format strings, nn denotes minutes, while mm usually denotes the month outside a time context.

Sub FormatDateAsText()
    ' Format a date with an explicit layout.
    Dim sampleDate As Date
    Dim dateText As String

    sampleDate = DateSerial(2026, 11, 3)
    dateText = Format$(sampleDate, "yyyy-mm-dd")
    Debug.Print dateText
End Sub

The example prints 2026-11-03. Format$ returns a String, while Format returns a Variant containing a string. Both are commonly used for formatting. A pattern such as "0.00" can request two displayed decimal places, but be aware that formatting conventions can still be affected by regional settings. If another application requires a strict interchange format, define and verify the exact conventions that application expects.

Special cases: Null, Empty, errors, objects, and arrays

The most important exception is Null. Calling CStr on Null raises run-time error 94, “Invalid use of Null.” This can arise when a Variant receives a database field that has no value. Test with IsNull before conversion and decide what the application should do: skip the value, use an empty string, or record a meaningful marker.

Empty is different from Null. It is the uninitialized state of a Variant, and CStr(Empty) returns a zero-length string. An empty worksheet cell may be read as an Empty Variant, but a cell containing a formula that returns "" is a string of zero length. Those cases can look alike on a sheet while having different underlying values.

An Excel error value, such as #N/A, should be detected with IsError before conversion. An object reference and an array are not ordinary scalar values, so passing either directly to CStr is not a general way to obtain useful text. For an object, decide which property should supply the text; for an array, decide how its elements should be joined or processed.

A defensive helper can make a deliberate choice for common special values. This example returns descriptive markers; change those markers if the application needs a different policy.

Function SafeText(ByVal value As Variant) As String
    ' Handle non-scalar and special Variant values before conversion.
    If IsError(value) Then
        SafeText = "[Excel error]"
    ElseIf IsNull(value) Then
        SafeText = "[Null]"
    ElseIf IsEmpty(value) Then
        SafeText = ""
    ElseIf IsObject(value) Then
        SafeText = "[Object]"
    ElseIf IsArray(value) Then
        SafeText = "[Array]"
    Else
        SafeText = CStr(value)
    End If
End Function

This helper does not serialize objects or arrays; it labels them so the calling code can recognize them. A marker such as [Null] is also a design choice, not a universal representation. If downstream code treats an empty string as missing, preserving the distinction between Null and Empty may be important.

Converting values from Excel cells

In Excel VBA, a cell’s value and its visible appearance are not necessarily the same. If a cell contains the number 7 and uses a custom number format to display 007, then CStr(Range("A1").Value) converts the underlying value and normally yields "7", not the displayed "007". A number format is presentation, not a change to the stored number.

Use the cell’s .Text property when you specifically need what Excel currently displays. However, displayed text can depend on column width and formatting; a narrow column may show hash marks instead of the value. It is therefore not a reliable general-purpose way to serialize data. If leading zeros are part of an identifier, store the identifier as text or construct the required string deliberately.

.Value2 is often useful when reading worksheet values because it returns the underlying value without the Date and Currency subtypes used by .Value. It does not return the cell’s formatted appearance. Choose the property based on whether your code needs the stored value or the displayed text, then convert that value with CStr or a format pattern as appropriate.

CStr compared with Str and string concatenation

Str is another VBA conversion function, but it has different behavior: it can include a leading space for positive numbers and uses a period as the decimal separator. That can be useful in specific legacy code, but it is often surprising in user-facing output. For ordinary locale-aware conversion, CStr is usually clearer.

The & operator concatenates text. VBA can coerce values during concatenation, so an expression such as "Total: " & amount may work without an explicit call. Writing "Total: " & CStr(amount) documents the conversion and helps readers see that text is intended. Avoid using + as a substitute for concatenation: depending on operand types, it can perform numeric addition, making mixed expressions harder to reason about.

StrConv is not the general “convert any value to a string” function. It applies string transformations such as changing case or converting character representations. For converting a supported value to text, use CStr; for a chosen display pattern, use Format or Format$.

Common mistakes and practical checks

  • Expecting cell formatting to be retained: CStr converts a value, not the cell’s number format. Use an explicit format when the output needs fixed decimal places or date ordering.
  • Assuming dates are universal: a plain date conversion can vary with regional settings. Specify the output pattern when consistency matters.
  • Converting Null without checking: test with IsNull first to avoid run-time error 94.
  • Treating text and numbers as interchangeable: after conversion, arithmetic operations may require converting the string back to a number. Keep values numeric for calculations and convert at the point where text is needed.
  • Expecting leading zeros to survive: numeric values do not remember zeros used only for display. Keep codes and identifiers as text or build the desired representation.

For debugging, inspect both the value and its type. The VBA functions TypeName and VarType can help identify what a Variant contains before conversion. This is especially useful when a routine processes a range that may contain numbers, text, blanks, dates, or worksheet errors.

Frequently Asked Questions

What is the VBA function to convert a number to a string?
Use CStr(number). For example, CStr(42) returns the text "42". If you need a particular number of decimal places, use Format or Format$ with an appropriate pattern.

How do I convert a date to text in VBA?
Use CStr(dateValue) for a locale-dependent representation. Use Format$(dateValue, "yyyy-mm-dd") when you want that explicit date layout; choose a pattern that suits the receiving system.

Why does CStr give an error on Null?
Null represents the absence of a valid value in a Variant, and CStr(Null) raises run-time error 94. Check with IsNull and choose how your code should represent or handle it.

Does CStr preserve leading zeros or Excel cell formatting?
No. It converts the underlying value, not the worksheet’s display format. Store identifiers as text or create a formatted string explicitly; use a cell’s .Text only when its current displayed appearance is what you need.

Should I use CStr or Str?
Usually use CStr for ordinary value-to-string conversion. Str has distinctive numeric behavior, including a leading space for positive values and a period as the decimal separator, so use it only when those details are wanted.

Related

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.