VBA: Check Whether a String Contains Text

VBA & macros · Updated November 22, 2026

Yes. In VBA, use the InStr function to check whether one string contains another: it returns the position of the match, or 0 if there is no match. For a simple yes-or-no test, write InStr(1, text, searchText, vbTextCompare) > 0 to search without distinguishing uppercase and lowercase letters.

What “VBA is in string” usually means

The phrase “VBA is in string” is often shorthand for asking whether VBA can find a word or sequence of characters inside a longer piece of text. For example, a macro might need to determine whether a cell contains “late,” whether a file path includes a particular folder name, or whether a customer comment mentions “refund.” VBA can test these cases without changing the original text.

The key distinction is between searching for text and comparing two complete strings. An equality test such as value = "Approved" checks whether the entire value is exactly “Approved.” It does not return true for “Approved yesterday.” InStr, by contrast, can find “Approved” within that longer phrase.

How InStr works

InStr takes a starting position, the string to search, the text to look for, and optionally a comparison setting. Its result is a number: the character position where the match begins, counting from 1, or 0 when no match is found. Because the result is numeric, a presence check normally compares it with zero.

For example, searching for “sales” in “Quarterly sales report” returns 11: the “s” in “sales” is the eleventh character. Searching the same phrase for “profit” returns 0. A return value of 1 means the match starts at the first character; it does not mean the text was found only once.

  • InStr(1, text, searchText, vbBinaryCompare) performs a case-sensitive comparison.
  • InStr(1, text, searchText, vbTextCompare) performs a case-insensitive text comparison.
  • InStr(1, text, searchText, 0) is equivalent to using vbBinaryCompare.

Using the named constants makes the intent easier to read than using 0 or 1. If you leave out the comparison argument, the comparison mode depends on the module’s Option Compare setting. A module without that setting uses binary comparison by default, so specifying vbTextCompare directly is a reliable way to show that letter case should not matter.

A basic VBA example

This macro checks whether “sales” appears in a sample phrase. It prints the match position in the Immediate window if found, and a message if it is absent. The example uses a case-insensitive search, so it would also match “Sales” or “SALES.”

Option Explicit

Sub CheckForText()
    Dim sourceText As String
    Dim searchText As String
    Dim foundAt As Long

    sourceText = "Quarterly sales report"
    searchText = "sales"

    ' Search without distinguishing letter case.
    foundAt = InStr(1, sourceText, searchText, vbTextCompare)

    If foundAt > 0 Then
        Debug.Print "Found at position " & foundAt
    Else
        Debug.Print "Text was not found."
    End If
End Sub

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and place the code in that module. Run the macro from the editor or through Excel’s macro controls. On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well, and the shortcut can vary with keyboard settings. Excel for the web does not run VBA macros.

This example only writes to the Immediate window, but it is good practice to save a copy of a workbook before running any macro. Macro actions that change workbook contents cannot be undone with Ctrl+Z.

Choose whether the search is case-sensitive

Case sensitivity matters when uppercase and lowercase letters represent different values. For ordinary prose, searching for “invoice” regardless of capitalization is often more useful, so specify vbTextCompare. For identifiers, codes, or text where the exact capitalization matters, use vbBinaryCompare. With binary comparison, “A7” and “a7” are different strings.

Text comparison follows language-related comparison rules, which can depend on the system’s locale. If a task requires exact character-by-character matching, binary comparison is the clearer choice. Also remember that a case-insensitive search does not ignore spaces, punctuation, accents, or spelling differences. A search for “order number” will not automatically match “order-number” or “order number” with two spaces.

You can set Option Compare Text at the top of a VBA module to make string comparisons in that module use text comparison by default. However, this affects more than one InStr call and can make behavior less obvious when someone later reads the code. Passing vbTextCompare or vbBinaryCompare in each search makes the intended behavior explicit.

Make a reusable function

If several macros need the same test, place the logic in a function and call it wherever needed. This version returns a Boolean value, meaning True or False. It treats an empty search term as no match, a useful safeguard when a search phrase comes from a blank cell or a user input box.

Option Explicit

Public Function ContainsText(ByVal sourceText As String, _
                             ByVal searchText As String) As Boolean
    ' An empty search term is treated as no match.
    If Len(searchText) = 0 Then
        ContainsText = False
        Exit Function
    End If

    ContainsText = (InStr(1, sourceText, searchText, vbTextCompare) > 0)
End Function

In a macro, you can use the function in a condition such as If ContainsText(comment, "refund") Then. It can also be called from an Excel worksheet after the function is stored in a standard VBA module and macros are permitted. For example, enter =ContainsText(A2,"refund") in a cell to test the text in A2. A worksheet user-defined function is not the same as a built-in Excel function: the workbook must be macro-enabled, and the function will not calculate in Excel for the web.

VBA strings are commonly stored as String variables, while worksheet values can arrive in VBA as other types, including numbers, errors, or empty cells. The function above accepts strings, so a macro that supplies values from cells should consider how to handle blanks and error values before passing them in. For a controlled text column, reading the displayed text or converting a known value with CStr may be appropriate; do not assume every cell value can safely be treated as ordinary text.

Search text in worksheet cells

To check one cell from a macro, read its value and test it with InStr. For instance, a macro can inspect a comment in A2 and branch depending on whether it includes “refund.” When working with many rows, a loop can apply the same test to each cell and write a result to a neighboring column. The search itself remains the same; the loop determines which cells are examined and what to do with the result.

For a macro that checks a worksheet cell, first decide how to handle cells that are blank, contain an Excel error, or contain a number rather than text. A direct assignment of a cell’s value to a String variable may convert some values, but an error value cannot be treated as an ordinary string. Robust code checks for errors and empty values before searching. It should also refer to a specific worksheet, rather than relying on whichever sheet happens to be active, so that the macro searches the intended data.

If the task is simply to find matching cells interactively, a macro may not be needed. In Excel for Microsoft 365 on Windows, use Home > Find & Select > Find, or press Ctrl+F, and enter the text. The Find dialog can search within the sheet or workbook and offers options such as matching case. On Mac, use Command+F to open Find. Interactive Find is convenient for a person locating a value; InStr is useful when VBA needs to make a decision or automate a response.

Common mistakes and edge cases

A frequent mistake is treating the numeric return from InStr as if it were already a Boolean. VBA can coerce values in some expressions, but explicitly writing > 0 makes the test clear and avoids confusing the found position with a yes-or-no answer. Another mistake is checking whether the result equals 1; that only finds text beginning at the first character and misses valid matches later in the string.

  • Empty search term: An empty substring can produce a match at the starting position. If blank input should mean “no search,” check Len(searchText) before calling InStr.
  • Wrong comparison mode: A binary search for “paid” will not match “Paid.” Choose the comparison behavior intentionally.
  • Unexpected spaces: Leading, trailing, or repeated spaces are real characters. If spaces should not affect the result, trim or normalize the relevant values deliberately.
  • Errors from cells: A worksheet error such as #N/A is not ordinary text. Check for an error value before converting or searching it.
  • Whole-word expectations: InStr finds character sequences, not whole words. Searching for “art” also matches “party.”

When whole-word matching is required, a plain substring test is not sufficient. You need additional logic to examine the characters before and after the match, taking punctuation, spaces, and the beginning or end of the string into account. The definition of a “word” can vary, especially for names and text in different languages, so decide which characters count as word boundaries before implementing that rule.

InStr compared with other options

InStr is the direct choice for asking whether a string contains a smaller piece of text. InStrRev is a related VBA function that searches from the end of a string toward the beginning; it is useful when the last occurrence matters, such as locating the final backslash in a path. It still returns a position or 0 and is not a whole-word search.

Excel’s worksheet SEARCH and FIND functions also locate text, but they are worksheet functions rather than VBA syntax. SEARCH is not case-sensitive, while FIND is case-sensitive. In VBA code, use the VBA function InStr rather than writing a worksheet formula as though it were a VBA statement. For a quick manual lookup, Excel’s Find command may be simpler; for repeatable program logic, VBA can test the result and take an action.

Frequently Asked Questions

How do I check if a string contains text in VBA?
Use If InStr(1, sourceText, searchText, vbTextCompare) > 0 Then for a case-insensitive check. The condition is true when the substring occurs anywhere in the source text.

Is InStr case-sensitive in VBA?
It depends on the comparison setting. Use vbBinaryCompare for case-sensitive matching and vbTextCompare for case-insensitive text matching. If the argument is omitted, the module’s comparison setting applies.

What does InStr return when there is no match?
It returns 0. A positive number is the starting character position of the first match, counting from 1.

Can I use InStr in an Excel worksheet formula?
InStr is a VBA function, not a built-in worksheet formula. You can use it in a macro or expose a VBA function as a user-defined worksheet function, but that requires a macro-enabled workbook and will not run in Excel for the web.

Does InStr find a whole word?
No. It looks for a sequence of characters anywhere in the string. For example, searching for “art” also matches “party.” Whole-word matching requires extra boundary checks or a different approach suited to the text.

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.