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
VarTypeand compare it withvbString. - 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 withIsErrorbefore callingCStror passing the value toInStr. - 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:
InStrsearches the string conversion of the value. UseVarType(value) = vbStringfirst 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
- Excel Slicers for Multiple PivotTables: How to Connect
- Excel Named Ranges in Data Validation: How to Use Them
- Excel Unable to Remove Duplicates? Common Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Excel: Copy a Range of CellsTo copy a range of cells in VBA, use the range’s Copy method and specify the top-left destination…
- Turn On AutoFilter in VBA: Code and ExamplesTo turn on AutoFilter with VBA, select the data range, including its header row, and call its AutoFilter…
- Excel VBA Loop Through a Folder: Practical ExamplesTo loop through files in a folder with Excel VBA, use the Dir function to find a matching…
- Excel VBA Sub or Function Not Defined? Fix It“Sub or Function not defined” is a VBA compile error: Excel cannot resolve the name of a procedure…
- Excel VBA Loop Over Rows: Examples and Best PracticesTo loop over rows in Excel VBA, use a For...Next loop from the first data row to the…
- Excel VBA UserForm Background Color: How to Change ItTo change an Excel VBA UserForm’s background color, set its BackColor property: in the form’s code module, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.