Excel VBA: Check Whether a Cell Contains Text

VBA & macros · Updated November 21, 2026

To check whether a cell contains a particular string in Excel VBA, use InStr and test whether its result is greater than zero. For example, If InStr(1, CStr(Range("A1").Value2), "apple", vbTextCompare) > 0 Then finds “apple” anywhere in A1 without distinguishing uppercase and lowercase; a different test is needed if you mean “is the cell’s value text?” or simply “is it nonblank?”

What “has a string” can mean in VBA

The phrase “cell has a string” can describe several different checks. Choosing the right one matters: a cell containing the number 42, a formula returning an empty string, and a cell containing the text “42” may look similar on a worksheet but have different underlying values.

  • Contains a substring: Find a sequence of characters somewhere in the cell, such as “east” in “Northeast.” Use InStr.
  • Is a text value: Check whether the value’s VBA type is String. Use VarType and compare it with vbString.
  • Is not blank: Check whether the value has any characters after converting it to text, or use a blank-specific test when that is the actual requirement.
  • Matches the whole cell: Compare the cell’s value with the search term using StrComp, rather than searching for a substring.

In all of these cases, VBA reads the cell’s value; it does not decide based on how the cell looks. A numeric value formatted to display as text is still numeric, and a number stored as text is a string. Use Value2 for a straightforward read of the underlying value, and handle error values before converting them to a string.

Find a substring with InStr

InStr searches one string for another. Its first argument is the starting position, its second is the text to search, and its third is the text to find. The optional comparison argument controls whether letter case matters. The following complete macro checks A1 on a worksheet named Sheet1:

Sub CheckForSubstring()
    Dim target As Range
    Dim searchText As String

    ' Set the cell and literal text to search for.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    searchText = "apple"

    ' Avoid converting an Excel error value to text.
    If IsError(target.Value2) Then
        MsgBox "A1 contains an error value."
    ElseIf InStr(1, CStr(target.Value2), searchText, vbTextCompare) > 0 Then
        MsgBox "The cell contains the search text."
    Else
        MsgBox "The cell does not contain the search text."
    End If
End Sub

vbTextCompare makes the search case-insensitive, so “APPLE” also matches. Use vbBinaryCompare when uppercase and lowercase must be treated as different characters. The search above is literal: the text “apple” must occur in sequence, but it may have other characters before or after it. If the search term is empty, InStr can report a match, so validate a term that users can enter before searching.

Check whether the cell’s value is a string

If the requirement is to accept text values only, rather than any value whose displayed form contains characters, use VarType. This test is useful when numbers, dates, and Boolean values should not count as text:

Sub CheckStringType()
    Dim target As Range
    Dim cellValue As Variant

    ' Read one cell from the specified worksheet.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    cellValue = target.Value2

    If IsError(cellValue) Then
        MsgBox "A1 contains an error value."
    ElseIf VarType(cellValue) = vbString Then
        MsgBox "A1 contains a string value."
    Else
        MsgBox "A1 does not contain a string value."
    End If
End Sub

A value entered as the text “42” normally has type String, while the numeric value 42 has a numeric type. The cell’s number format does not turn a number into a string. Also note that a formula returning "" returns a zero-length string: it passes the type test even though it appears blank. If your definition of “has a string” requires at least one character, add a length check after confirming that the value is a string.

Test for a nonblank value

Sometimes “has a string” really means “is not empty.” Those are not equivalent to a type check. A cell may contain a number or a date and still be nonblank, while a formula may return an empty string and appear blank despite containing a formula. To test for characters after safely converting a normal value, use Len:

Sub CheckForNonemptyText()
    Dim target As Range
    Dim cellValue As Variant

    ' Read the value from one cell.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    cellValue = target.Value2

    If IsError(cellValue) Then
        MsgBox "A1 contains an error, not ordinary text."
    ElseIf Len(CStr(cellValue)) > 0 Then
        MsgBox "A1 has a nonempty value when converted to text."
    Else
        MsgBox "A1 is empty or returns an empty string."
    End If
End Sub

This is a test of the converted value, not proof that the original value was text. A number such as 42 converts to a nonempty string and passes. A cell containing only spaces also passes, because spaces have length. If spaces should count as blank, trim ordinary leading and trailing spaces before measuring the length; for data copied from websites, remember that nonbreaking spaces and other unusual whitespace may need separate cleanup.

Search a range and mark matching rows

For a list, loop through individual cells instead of passing a multi-cell range to CStr. A multi-cell range can return an array of values, which is not a single string. This macro searches A2:A100 and writes a status in the neighboring B cell:

Sub MarkSubstringMatches()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchText As String

    ' Choose the worksheet and search term.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    searchText = "apple"

    ' An empty search term would match unexpectedly, so stop here.
    If Len(searchText) = 0 Then Exit Sub

    ' Check each cell separately and record the result in column B.
    For Each cell In ws.Range("A2:A100").Cells
        If IsError(cell.Value2) Then
            cell.Offset(0, 1).Value = "Error value"
        ElseIf InStr(1, CStr(cell.Value2), searchText, vbTextCompare) > 0 Then
            cell.Offset(0, 1).Value = "Match"
        Else
            cell.Offset(0, 1).Value = "No match"
        End If
    Next cell
End Sub

Change the sheet name, input range, search term, and output location to fit the workbook. If column B contains existing information, this example will overwrite it, so choose an unused output column or adjust the code. To highlight cells rather than write labels, change the matching branch to set a cell’s formatting; clearing old highlights before a repeat run may be necessary so former matches do not remain colored.

Make the comparison exact or case-sensitive

InStr is appropriate when the search text may appear anywhere inside a longer value. For example, searching for “cat” also finds “catalog.” If the entire cell must equal the search term, use StrComp and test whether the result is zero. The comparison mode can still be case-insensitive or case-sensitive.

Sub CheckExactMatch()
    Dim target As Range
    Dim searchText As String
    Dim cellValue As Variant

    ' Set the cell and the complete value expected.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    searchText = "Open"
    cellValue = target.Value2

    If IsError(cellValue) Then
        MsgBox "A1 contains an error value."
    ElseIf StrComp(CStr(cellValue), searchText, vbTextCompare) = 0 Then
        MsgBox "The whole cell matches, ignoring letter case."
    Else
        MsgBox "The whole cell does not match."
    End If
End Sub

For an exact case-sensitive match, replace vbTextCompare with vbBinaryCompare. Neither test automatically removes spaces at the beginning or end. If the data may have accidental spaces, decide whether to trim them before comparing; silently trimming can be wrong when spaces are meaningful in the source data.

Common errors and practical fixes

  • Type mismatch when converting a cell: The cell may contain an Excel error such as #N/A. Test with IsError before calling CStr or passing the value to InStr.
  • Every value seems to match: Check that the search term is not empty. An empty search string does not behave like an ordinary word search.
  • A number unexpectedly passes: InStr searches the string conversion of the value. Use VarType(value) = vbString first if numbers must be excluded.
  • A visual blank passes the type check: A formula returning an empty string is still a string. Check its length if the requirement is at least one character.
  • Only one cell is being searched: Qualify references with a worksheet and loop over the range. Avoid relying on whichever sheet happens to be active.

For reliable code, keep the reference explicit, decide how errors and blanks should be treated, and use the comparison mode that matches the task. If a search value comes from a cell or a user prompt, validate it once before the loop rather than allowing an empty term to mark every row as a match.

Run the macro safely in Excel

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, insert a standard module through Insert > Module, and paste the macro there. Run it from the editor with the cursor inside the procedure and press F5, or return to Excel and use Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

On Excel for Mac, VBA is available in the desktop app, but keyboard mappings and some features differ; the Visual Basic Editor shortcut is commonly Option+F11, sometimes with Fn depending on the keyboard. Excel for the web does not run VBA macros, so use desktop Excel to execute this code. Save a copy of the workbook before running any macro that changes cells: macro actions cannot be undone with Ctrl+Z. Save a macro-enabled workbook as .xlsm, and enable macros only for files you trust.

Frequently Asked Questions

How do I check if a cell contains a specific word in VBA?
Use InStr(1, CStr(cell.Value2), "word", vbTextCompare) > 0 after checking that the value is not an Excel error. This finds the sequence anywhere in the cell and ignores letter case.

How do I check whether a cell contains text and not a number?
Read its value into a Variant, handle errors with IsError, and test VarType(value) = vbString. A number stored as text is a string; a numeric value displayed with a text-like format is still numeric.

How can I check whether a cell is not blank?
For ordinary values, check for an error first and then test Len(CStr(value)) > 0. This treats numbers as nonblank and treats a formula returning an empty string as blank after conversion, so choose the test that matches your definition.

Does InStr distinguish uppercase and lowercase?
It depends on the comparison argument. Use vbTextCompare for a case-insensitive search and vbBinaryCompare for a case-sensitive one; specifying the mode makes the intention clear.

Can I use this VBA code in Excel for the web?
No. Excel for the web can open workbooks containing VBA, but it does not execute VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the code.

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.