Yes—VBA can check whether one string contains another, but it does not have a built-in string method named Contains. The usual tool is InStr: it returns the position of a match, so a result greater than zero means the text was found.
What InStr does
InStr searches one string for a second string. For example, it can check whether a message contains a word, whether a filename includes a particular extension, or whether a cell value includes a code. Unlike a Boolean-style Contains method in some other programming languages, InStr reports where the match begins.
That position is a number counted from 1, not 0. If the text is absent, the result is 0. You can use that return value directly in an If statement, but an explicit comparison with zero makes the intent clear:
If InStr(1, sourceText, searchText, vbTextCompare) > 0 Then
' The search text occurs in sourceText.
End If
For instance, searching Quarterly Sales Report for Sales returns 11, because the S begins at the eleventh character. Searching for Budget returns 0. This makes InStr useful both for a yes-or-no test and for extracting or inspecting text around a match.
Syntax and return values
The general syntax is InStr([start,] string1, string2, [compare]). Here, string1 is the text being searched and string2 is the text to look for. The optional start argument sets the position from which the search begins; if left out, the search starts at position 1. The optional compare argument controls how VBA treats letter case.
start: the character position at which to begin searching, starting at 1.string1: the complete string to search.string2: the substring, sometimes called the search term.compare: a comparison mode such asvbBinaryCompareorvbTextCompare.
For example, InStr(1, "green apple", "apple") returns 7. Starting at position 8 in that same string would return 0, because the match begins before the requested starting point. If the search term is an empty string, InStr returns the starting position; that edge case can matter when a search term comes from a cell that might be blank.
The return value is normally a Long position. When the inputs are Variants that contain Null, the result can also be Null; code that accepts uncertain data should check for Null before treating the result as an ordinary number. In typical Excel VBA work, variables declared as String and known to contain text keep the result straightforward.
Choose case-sensitive or case-insensitive matching
By default, InStr uses the module’s comparison setting. Unless the module specifies otherwise with an Option Compare statement, VBA uses binary comparison, which distinguishes uppercase from lowercase. Thus, a search for cat will not match Cat under binary comparison.
To choose the behavior at the point of the search, pass a comparison constant as the fourth argument:
vbBinaryComparemakes the search case-sensitive.vbTextComparemakes the search case-insensitive according to VBA’s text comparison rules.
For example, InStr(1, "Cat in the garden", "cat", vbTextCompare) returns 1. With vbBinaryCompare, it returns 0. Providing the comparison mode explicitly is often a good choice in reusable procedures because the result does not depend on a module-level setting that a later reader might overlook.
You can also set Option Compare Text at the top of a module, before its procedures, to make text comparison the module’s default. However, that changes the default comparison behavior for other relevant string comparisons in that module too. Use it deliberately; passing vbTextCompare to a particular call makes the choice easier to see locally.
A practical Excel VBA example
This macro checks a sample phrase and reports the match position. The phrase begins with Quarterly (9 characters), followed by a space, so Sales starts at position 11. Before running any macro that changes a workbook, save a separate copy: VBA macros cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, run a macro from Developer > Macros; on a Mac, use Tools > Macro > Macros or the Macros command on the Developer tab, depending on the ribbon setup.
Sub CheckForText()
Dim sourceText As String
Dim searchText As String
Dim matchPosition As Long
' Set the example text and search term.
sourceText = "Quarterly Sales Report"
searchText = "sales"
' Ignore case and save the character position.
matchPosition = InStr(1, sourceText, searchText, vbTextCompare)
If matchPosition > 0 Then
MsgBox "Found at character " & matchPosition
Else
MsgBox "Text was not found."
End If
End Sub
The message in this example says the match is at character 11. To test only whether there is a match, you do not need to store the position: use If InStr(1, sourceText, searchText, vbTextCompare) > 0 Then. Keep the position when you need to report the location or use it in later string operations.
Searching text stored in worksheet cells
InStr searches string values; it does not search a worksheet range by itself. In a macro, read a cell’s value into a variable, check it, and then decide what action to take. For example, a loop could examine each row in a column and use InStr to identify descriptions containing a chosen word. Use a qualified worksheet reference, such as ThisWorkbook.Worksheets("Data"), rather than relying on whichever sheet happens to be active.
Cell values can be blank, numeric, or error values, so consider what your workbook actually contains. An empty cell normally behaves like an empty value when read, while an Excel error such as #N/A needs to be checked with IsError before converting or searching it as text. If data may come from Access or another source that supplies Null, check with IsNull as well. These checks prevent a text search from failing because the input is not ordinary text.
For repeated searches through many rows, avoid selecting cells just to inspect them. Reading values directly from a range is generally clearer and avoids changing the user’s selection. Also decide whether a match should be case-sensitive and whether the search term may be blank before you write the loop; both choices affect which rows qualify.
InStr compared with Like and Find
InStr is the simplest choice for a literal substring: it asks whether the exact sequence of characters occurs in a string, with optional case handling. The Like operator is different. It checks whether an entire string matches a pattern, and pattern characters such as *, ?, and # have special meanings. For example, "*cat*" can match text with characters before and after cat, but a search term containing wildcard characters may not behave like a literal search.
Excel’s Range.Find method is another tool, but it searches cells in a range rather than acting as a substring function on a VBA string. It is suitable when you want Excel to locate a matching cell and return a Range object. Search options used by Excel’s Find dialog can affect Find behavior, so specify relevant arguments when predictable results matter. For a simple question about whether a variable contains text, InStr is usually more direct.
For a search that needs regular-expression features, such as validating a pattern or matching several alternative forms, a regular expression may be appropriate. That is more involved than a literal substring check and requires using the VBScript regular expressions library or another available implementation. Do not reach for it just to replace one InStr call.
Common mistakes and edge cases
- Expecting a Boolean result:
InStrreturns a position or zero, not the words True or False. Compare the result with zero. - Reversing the two strings: the first string after the optional start is the text to search; the next is the term to find.
- Overlooking letter case: make the desired behavior explicit with
vbBinaryCompareorvbTextCompare. - Passing a blank search term: an empty term is treated as a match at the start position. Decide whether blank input should instead be rejected.
- Confusing position with a character count: the result is the starting position of the first match, not the number of matching characters.
Another practical issue is what counts as a match. Searching for art in carton succeeds because those letters occur consecutively, even though art is not a separate word. If you need whole-word matching, a plain InStr test is not enough; you must also check the characters before and after the match, or choose a pattern-based approach suited to the text rules.
When a small helper function is useful
If a project repeatedly needs a yes-or-no answer, you can wrap InStr in your own function named ContainsText. This does not add a built-in method to VBA; it creates a project-level function that makes repeated calls easier to read. Put it in a standard VBA module and choose the comparison mode when calling it.
Public Function ContainsText(ByVal sourceText As String, _
ByVal searchText As String, _
Optional ByVal compareMode As VbCompareMethod = vbBinaryCompare) As Boolean
' Return True when the search term occurs in the source text.
ContainsText = (InStr(1, sourceText, searchText, compareMode) > 0)
End Function
Because this function uses InStr, an empty searchText returns True: the underlying search reports a match at position 1. If that is not appropriate for your task, add a separate check for an empty term before calling InStr. If inputs can be Null or error values, validate them before passing them to this function, since its parameters are declared as strings.
Frequently Asked Questions
Does VBA have a Contains function?
No built-in string method named Contains is provided for ordinary VBA strings. Use InStr and test whether its result is greater than zero.
How do I make InStr ignore uppercase and lowercase?
Pass vbTextCompare as the comparison argument, as in InStr(1, textToSearch, term, vbTextCompare). This makes the comparison case-insensitive.
What does InStr return when it cannot find the text?
It returns 0. A positive return value is the one-based position where the first match begins.
Does InStr find a whole word only?
No. It finds a sequence of characters anywhere in the string, including inside a longer word. Add boundary checks or use a suitable pattern method if whole-word matching is required.
What is the difference between InStr and InStrRev?InStr searches forward and returns the first match from its starting position. InStrRev searches backward for the last occurrence; it is useful when locating the final separator in a path or the last instance of a repeated delimiter.
Related
- X++ Enum Values in SQL: How Stored Numbers Work in D365
- How to Add Speaker Notes to Your PowerPoint Slides
- What Is Balance in Accounting? Meaning and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Editor: How to Open and Use ItThe Excel VBA Editor, also called the Visual Basic Editor or VBE, is the desktop window where you…
- VBA Code to Sort Excel Data by One or More ColumnsTo sort Excel rows with VBA, define the complete data range, add one or more sort keys, set…
- How to Open Macro Code in Excel: A Step-by-Step GuideIn Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, then use Project…
- Excel VBA: Change a PivotTable Data SourceTo change an Excel PivotTable’s source with VBA, create a new PivotCache from the replacement range, assign it…
- Excel VBA Like Statement: Patterns and ExamplesIn Excel VBA, the Like operator compares a string with a pattern and returns whether the string matches…
- Excel VBA Data Entry Forms: Setup, Code, and TipsAn Excel VBA data entry form is a custom window for collecting information and writing it to a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.