Excel VBA: Get a String from a Cell, Safely and Reliably

VBA & macros · Updated November 21, 2026

To get a cell’s value as a VBA string, read it into a Variant and convert it with CStr: use Value2 for the cell’s underlying value, then check for errors and blanks before converting. For example, rawValue = ws.Range("A1").Value2 followed by cellString = CStr(rawValue) works for ordinary text and numbers; the choice between Value2, Value, and Text matters when the cell contains dates, errors, or formatted values.

How VBA Reads a Cell

A worksheet cell is a Range object. Its Value and Value2 properties return a Variant, not necessarily a VBA String. A cell that looks like text may hold a number, a date serial, a formula result, or an Excel error. Assigning that result to a String requires conversion, and converting an error value directly can cause a type mismatch.

For a single cell, the basic pattern is to qualify the worksheet, read the value, check its type where needed, and convert it. Qualifying the worksheet makes the code independent of whichever sheet happens to be active. The workbook that contains the VBA code is referred to by ThisWorkbook; ActiveWorkbook means the workbook currently in front and may be a different file.

A Safe Example for One Cell

This macro reads A1 on Sheet1, handles an Excel error and a truly empty cell, and prints the resulting string in the VBA Immediate window. In the Visual Basic Editor on Windows, press Alt+F11 to open the editor and Ctrl+G to show the Immediate window. On Mac, Option+F11 opens the editor on keyboards where function keys are available; you may need Fn+Option+F11. Before running any macro, save a copy of the workbook: macro actions generally cannot be undone with Ctrl+Z.

Sub ReadCellAsString()
    Dim ws As Worksheet
    Dim rawValue As Variant
    Dim cellString As String

    ' Read from a specifically named worksheet.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    rawValue = ws.Range("A1").Value2

    ' Avoid converting an Excel error directly to a String.
    If IsError(rawValue) Then
        cellString = "[cell contains an Excel error]"
    ElseIf IsEmpty(rawValue) Then
        cellString = vbNullString
    Else
        cellString = CStr(rawValue)
    End If

    Debug.Print cellString
End Sub

Change Sheet1 and A1 to the actual worksheet name and address. If the sheet name contains spaces, the quoted worksheet name is still valid as shown. If you want to display the result instead of printing it, a message box can be used, but printing is less intrusive when a macro processes many cells.

Choosing Between Value2, Value, and Text

  • Value2 is a strong default when you want the underlying cell value. It returns dates and currency-formatted numbers as numeric values rather than VBA Date or Currency subtypes, which avoids some automatic conversions.
  • Value also returns the underlying value, but can return a Date or Currency subtype in certain circumstances. That may be convenient if you want VBA to recognize a date as a date, but it is not usually the simplest choice for consistent string conversion.
  • Text returns the cell’s displayed text, including the effect of number formatting. Use it when the visible representation is what matters, not when you need the actual stored value.

For example, a cell storing the number 0.25 might display as 25% with a percentage format. Converting Value2 to a string gives a representation of the underlying number, while Text gives the formatted display, such as 25%. The exact string conversion of numbers can depend on regional settings, so avoid relying on a particular decimal separator when moving data between computers.

Text has a further limitation: its result can depend on column width. A number too wide for the column may display as hash marks, and that is what the property can report. It can also return Null when asked for a multi-cell range whose cells do not all have the same displayed text. For ordinary data processing, read the value and format it deliberately rather than treating the screen display as the stored data.

Blanks, Formula Results, and Errors

A genuinely empty cell is represented by an Empty Variant, which IsEmpty can detect. By contrast, a formula that returns an empty string is not the same thing as an unused cell. Its value is a zero-length string, and converting that value with CStr produces an empty string. Decide whether those two cases should be treated alike in your application.

Excel errors such as #N/A and #VALUE! are special Variant values. Use IsError before calling CStr; otherwise, conversion can fail. The example substitutes a descriptive message, but other sensible policies include skipping the row, recording an error status separately, or stopping with a clear message. Choose a policy that will not mistake an error for valid text.

If a cell contains a formula, Value2 returns its calculated result, not the formula text. To inspect the formula itself, use the cell’s Formula property and store the result in a Variant or String as appropriate. A formula result may be text, a number, a blank string, or an error, so the same type checks remain useful.

Preserving Leading Zeros and Display Formatting

Converting a number to a string cannot recover information that Excel did not store. If A1 contains the numeric value 27 and a custom number format displays it as 00027, CStr converts the value, not the padded display, so the result is typically “27.” If the zeros are part of an identifier, store the identifier as text in the first place or explicitly apply the required formatting in VBA.

This distinction matters for postal codes, account codes, product IDs, and phone numbers. These are often identifiers rather than quantities, so arithmetic is not useful and leading zeros may be significant. If the source cell is already text, reading Value2 preserves its text. If it is numeric with a display format, decide whether you need the stored number or a formatted representation before choosing the property.

For a fixed format, format the numeric value intentionally rather than assuming CStr copies Excel’s number format. For variable formats or complicated display requirements, inspect the format and handle the relevant cases explicitly. Avoid using Text as a universal fix because it can depend on column width and may return a display result unsuitable for later calculations or data exchange.

Reading Different Cells and Worksheets

The sample uses one cell, but the same approach works for another address or a cell selected by row and column. Keep the worksheet reference explicit, especially in procedures that run while the user can switch sheets. A reference such as ActiveSheet.Range("A1") is shorter but can silently read the wrong sheet if the active sheet changes.

A multi-cell range is not a single string. Assigning its Value2 to a Variant generally gives an array of values, so code must loop through the cells or process the array. Do not assign a multi-cell result directly to a String and expect VBA to combine its contents. For a single cell inside a loop, convert each value separately and apply the same error and blank checks.

If a range belongs to another workbook, qualify that workbook too. ThisWorkbook refers to the file containing the macro, which may not be the workbook holding the data. A workbook variable or a deliberately chosen workbook reference is safer than relying on the active workbook. Also check that the worksheet name exists: a misspelled name causes a run-time error before the cell can be read.

Common Mistakes and Troubleshooting

  • Using an unqualified Range: Range("A1") relies on the active sheet. Set a Worksheet variable and use ws.Range("A1") when the intended sheet is known.
  • Converting an error value: test with IsError before CStr so an Excel error does not trigger a type mismatch.
  • Expecting formatted text from Value2: Value2 returns data, not the cell’s visual number format. Use Text only when the displayed result is genuinely required.
  • Expecting a formula string: Value2 returns the formula’s result. Read Formula when you need the formula expression.
  • Forgetting that a range has several cells: process cells individually or handle the returned array rather than treating the whole range as one string.

When a result seems wrong, inspect both the value and the cell’s number format in Excel, then check the Immediate window or use the VBA debugger to examine the Variant before conversion. Verify whether the cell is truly blank, contains a formula returning an empty string, or contains a number displayed as text. Those cases can look identical on the worksheet while requiring different handling.

Macros, File Types, and Excel Versions

VBA macros run in desktop Excel, including Excel for Microsoft 365 on Windows and Mac, but Excel for the web cannot run VBA macros. Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving it as a standard .xlsx file removes VBA project content. On Windows, the macro list can be opened with Alt+F8; on Mac, Option+F8 is commonly used, with Fn possibly needed for function keys.

Only run macros you trust. If a workbook came from another person or the internet, understand what its code does before enabling it, and follow your organization’s macro security rules. The example above only reads a cell and writes to the Immediate window, but a macro can also alter or delete workbook data. A backup is prudent before running unfamiliar code or adapting a procedure that changes cells.

Frequently Asked Questions

How do I convert a cell value to a string in VBA?
Read the cell’s Value2 into a Variant, handle errors if necessary, then use CStr. For example, the essential conversion is cellString = CStr(rawValue) after the value has been checked.

Should I use Value or Value2 in Excel VBA?
Use Value2 as a simple default when you want the underlying value without automatic Date or Currency subtypes. Use Value when those subtypes are useful to your code, and Text when you specifically need the displayed representation.

How can I get the text shown in a cell?
Use the cell’s Text property for its displayed text. Be aware that the result can be affected by number formatting and column width, so it is not always a dependable way to retrieve the underlying data.

Why does CStr fail on some cells?
A cell may contain an Excel error value, which cannot be treated like an ordinary number or string. Check with IsError before converting. Also verify that the reference is a single cell rather than a multi-cell range.

How do I keep leading zeros when reading a cell?
If the zeros are part of an identifier, store the value as text or build the required padded string explicitly. A number formatted to display leading zeros still stores a number, so CStr does not automatically preserve those display zeros.

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.