Excel VBA Empty String: How to Test, Set, and Clear

VBA & macros · Updated November 21, 2026

In Excel VBA, an empty string is a string with zero characters, written as "". Use Len(value) = 0 to test whether a string is empty; use IsEmpty(value) instead when you need to know whether a Variant has never been assigned a value. Those checks are not interchangeable, especially when a worksheet cell contains a formula that displays nothing.

What an empty string means in VBA

A string stores text. An empty string is still a string, but it contains no characters. You can assign one to a variable with text = "", compare a string with text = "", or check its length with Len(text) = 0. In each case, the double quotation marks are VBA’s way to represent text with nothing between the quotes.

This is different from a space. The string " " contains one space, so its length is 1. A tab or other whitespace character also makes a string nonempty, even if the value looks blank on screen. If your requirement is to treat whitespace-only text as blank, remove or account for whitespace before checking it.

Empty strings are useful when a procedure needs to represent “no text,” such as an optional description, a search term that has not been entered, or the result of a function that found nothing to return. They are ordinary string values, not a special worksheet display format.

Empty string, Empty, and blank are different

VBA’s Empty is a special value available to a Variant. A newly declared, uninitialized Variant has the value Empty. By contrast, a variable declared As String is initialized as an empty string. Consequently, IsEmpty is designed to test a Variant’s state, not to test whether a string contains zero characters.

  • Dim text As String gives you a string variable that starts with zero characters. IsEmpty(text) is not the appropriate test.
  • Dim value As Variant leaves the Variant uninitialized, so IsEmpty(value) returns True until it receives a value.
  • value = "" assigns a zero-length string to the Variant. It is no longer Empty, although its string length is zero.
  • A worksheet cell with no content is different from a cell containing a formula whose displayed result is an empty string.

This distinction explains many confusing results: “looks blank,” “contains no characters,” and “has never been assigned” describe different conditions. Choose a test based on which condition your code actually needs to detect.

How to test an empty string

For a variable declared as a String, Len(text) = 0 is a clear test for zero characters. Comparing the value directly with "" is also valid, but the length check makes the criterion explicit. For an uninitialized Variant, test IsEmpty(value) before deciding whether the Variant holds a string.

The following procedure separates an uninitialized Variant from an empty string and other values. It uses VarType to check that the Variant contains a string before examining its length. A Variant containing an Excel error value or Null needs its own handling if your code can receive those values.

Sub CheckValue()
    Dim value As Variant

    ' A newly declared Variant is Empty.
    If IsEmpty(value) Then
        Debug.Print "The Variant has not been assigned."
    ElseIf VarType(value) = vbString Then
        If Len(value) = 0 Then
            Debug.Print "The Variant contains an empty string."
        Else
            Debug.Print "The Variant contains text."
        End If
    Else
        Debug.Print "The Variant contains a non-string value."
    End If
End Sub

To try this code, open the Visual Basic Editor from the Developer tab’s Visual Basic command, insert a standard module, and run CheckValue. The messages appear in the Immediate window, which you can show in the editor with Ctrl+G on Windows. On a Mac, open the Visual Basic Editor from the Developer tab and use its View menu to show the Immediate window; keyboard shortcuts can vary with the keyboard and macOS settings.

Assigning and returning an empty string

Assign "" when you want a String variable to contain no characters. A function whose return type is String can also return "" to indicate that it has no text result. This does not automatically mean the function failed; document what an empty return means so that calling code can distinguish “no match” from a valid but empty result.

Function GetLabel(ByVal useShortLabel As Boolean) As String
    ' Return no text when a short label is not requested.
    If useShortLabel Then
        GetLabel = "Short label"
    Else
        GetLabel = ""
    End If
End Function

VBA also provides the constant vbNullString, which represents a null string pointer and is commonly used where an empty string is expected. In ordinary string comparisons and length checks, it behaves as a zero-length string. For most application code, "" is easier to read; use vbNullString when it suits an established coding convention or an API declaration that specifically expects it. It is not the same as the Variant value Null.

Checking worksheet cells with VBA

When you read a cell through Range.Value2, a genuinely blank cell can yield an Empty Variant. A cell containing a formula such as =IF(A1=0,"",A1) instead yields a zero-length string when that formula returns "". The cell may look blank in the grid in both cases, but VBA can distinguish them.

This macro checks cell A1 first for a genuinely blank value and then for a formula result or other string with zero characters. It reports the result in the Immediate window rather than changing the workbook.

Sub CheckA1()
    Dim value As Variant

    value = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value2

    If IsEmpty(value) Then
        Debug.Print "A1 is genuinely blank."
    ElseIf VarType(value) = vbString Then
        If Len(value) = 0 Then
            Debug.Print "A1 contains a zero-length string."
        Else
            Debug.Print "A1 contains text."
        End If
    Else
        Debug.Print "A1 contains a value that is not a string."
    End If
End Sub

Replace Sheet1 with the actual worksheet name in your workbook. Before running any macro that edits cells, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. The example above only reads a value and prints a message, so it does not edit the worksheet.

Clearing a cell versus writing an empty string

These actions can look alike on the worksheet but leave different contents behind. To remove a cell’s value or formula, use the range’s ClearContents method. To put a zero-length string into a cell, assign "" to its Value or Value2 property. The latter writes a value; it does not remove the existing cell content in the same sense as clearing it.

For example, this macro clears the contents of A1 while retaining its formatting. Save a copy of the workbook before running it, because a macro’s changes cannot be reversed with Ctrl+Z.

Sub ClearA1Contents()
    Dim target As Range

    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    target.ClearContents
End Sub

By comparison, target.Value2 = "" assigns an empty string to the cell. Use ClearContents when the goal is to remove a value or formula, and use an empty-string assignment when a zero-length text value is specifically what the procedure should write. Neither operation removes formatting; methods such as clearing all cell formats have a different effect.

Whitespace, Null, and error values

A common mistake is to treat every visually blank value as an empty string. A cell with one or more spaces is not empty, and Len(" ") returns 1. If spaces should count as blank in your application, trim the text before checking it. For example, with a String variable named text, Len(Trim$(text)) = 0 checks whether removing leading and trailing ordinary spaces leaves no characters. It does not remove every possible whitespace character, such as tabs or nonbreaking spaces.

Null is another distinct value, often encountered when working with databases or other external data. It means that the value is unknown or missing in a way represented by Null; it is not an empty string and not Empty. Use IsNull(value) when a Variant may contain Null. If a Variant could also contain an Excel error value, check it with IsError(value) before attempting string operations. Otherwise, calling Len on a value that is not suitable for that operation can cause a runtime error.

For a normal String variable that cannot contain Null or worksheet error values, a simple length check is usually enough. For a Variant received from a cell, database, or user-defined function, first consider which value types are possible and handle them deliberately.

Choosing the right test for the job

Pick the test according to the source and meaning of the value, rather than its appearance. The following guide covers the most common cases:

  • String variable: use Len(text) = 0 to detect zero characters.
  • Variant that may not have been assigned: use IsEmpty(value) to detect the special Empty state.
  • Worksheet cell that must be truly blank: read its value into a Variant and test IsEmpty(value).
  • Worksheet cell where blank and formula-produced empty text both count as blank: check for an Empty value or a zero-length string, while accounting for errors if they are possible.
  • Text where spaces should count as blank: trim the string before testing, and decide whether tabs or other whitespace also need to be removed.

On the worksheet, a formula that returns "" is not the same as a cleared cell: the formula remains in the cell even though its displayed result is empty. In VBA, the cell’s value and its formula are separate things you can inspect. If your task is to find out whether the cell contains a formula, test the range’s HasFormula property rather than relying on IsEmpty or Len.

Frequently Asked Questions

Does IsEmpty detect an empty string in VBA?
No. IsEmpty tests whether a Variant has the special Empty value. A Variant assigned "" contains a string, so use Len(value) = 0 to test whether that string has zero characters.

What is the difference between “” and vbNullString?
Both are commonly used to represent a zero-length string in VBA code, and ordinary length checks treat them as having no characters. "" is the straightforward text literal; vbNullString is a VBA constant with a null string pointer representation. Neither is the Variant value Null.

How can I tell whether a cell is truly blank or a formula returns “”?
Read the cell’s Value2 into a Variant. A truly blank cell can be Empty, while a formula returning "" produces a zero-length string. Test for Empty first, then check the string’s length if you need to distinguish the cases.

Does assigning “” delete a cell’s contents?
It assigns a zero-length string; it is not the same operation as clearing the cell. Use Range.ClearContents when you intend to remove a cell’s value or formula while leaving its formatting intact.

How do I treat a cell containing only spaces as empty?
Read the value as text and trim it before checking its length, for example with Len(Trim$(text)) = 0 when text is a String. If tabs, line breaks, or nonbreaking spaces are possible, handle those characters explicitly too.

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.