InStr is the VBA and Microsoft Access function that returns the 1-based position of the first occurrence of one string inside another, and the biggest practical risk is not the syntax but the silent assumptions around case, Null values, empty search text, and what a zero result really means. Microsoft defines the return as a Variant containing a Long, not as a Boolean, so reliable code should treat the value as a position contract rather than a simple yes-or-no check (Microsoft, 2022a).
That distinction matters because text search often sits in the middle of a larger action. A macro may find a colon before extracting a status, detect a word before hiding a worksheet, locate an at-sign before parsing an address, or find a period before splitting an IP address. If the delimiter is missing, the search term is blank, or case rules change between modules, the next line can fail or quietly return the wrong data. Readers who are still building the foundations behind variables, strings, conditions, and functions can use our basic coding concepts guide as a companion.
This guide goes beyond a syntax walkthrough. It maps every important return case, explains why explicit comparison rules make code easier to move between Excel and Access, shows guarded parsing patterns, compares nearby string tools, and gives a test matrix that catches common edge cases before a macro reaches production. It also looks ahead to 2027, when desktop VBA and cloud-oriented Office Scripts are likely to coexist rather than collapse into one automation model.
The Return Value Is a Contract, Not a Yes/No Answer
The call shape is compact:
InStr([start], string1, string2, [compare])
string1 is the text being searched. string2 is the text to find. start is the 1-based character position where the scan begins. compare controls how characters are compared. Microsoft documents the result as the position of the first match, with zero used when no match is found (Microsoft, 2022a).
The part that causes bugs is the set of edge conditions around that normal case.
| Condition | Result | Why it matters |
| Match found | 1-based position | The first character is position 1, not 0. |
| No match | 0 | Test before passing the value into Left$, Mid$, Right$, or Characters. |
| string1 is “” | 0 | An empty source cannot contain a non-empty target. |
| string2 is “” | start | A blank search term can look like a successful match. |
| Either searched string is Null | Null | Variant inputs need a deliberate Null policy. |
| start is beyond the searchable text | 0 | A calculated starting point can skip all remaining matches. |
A subtle point follows from start: it changes where searching begins, but it does not reset the numbering. If a second match occurs at character 32 and the scan begins at character 4, the result is still 32. AutomateExcel calls out this absolute-position behavior in its current tutorial, and Microsoft’s own example shows a later start returning the original position in the full string (Microsoft, 2022a; AutomateExcel, 2024).
Use Long for position variables
One useful finding from our ranking-page review is that some current tutorials still demonstrate search positions with Integer variables. Bansal’s 2026 TrumpExcel example uses Dim Position As Integer, and ExcelMojo uses Integer for several position values. Microsoft, however, specifies a Variant containing a Long for the function result, while VBA Integer is limited to -32,768 through 32,767 (Bansal, 2026; ExcelMojo, 2026; Microsoft, 2022d).
For short worksheet labels, both types appear to work. For reusable code, Long is the safer match to the documented contract:
Dim pos As Long
pos = InStr(1, sourceText, targetText, vbBinaryCompare)
This is also a useful dividing line between VBA functions and worksheet formulas. The function discussed here is a VBA/Access function, not a normal worksheet formula. If your task belongs in cells rather than a macro, the broader spreadsheet formulas guide covers formula-based text and lookup workflows.
Comparison Rules Can Change Outside the Function Call
Case sensitivity is not merely a convenience setting. It can change whether a record is classified, whether a sheet is selected, or whether a branch of code runs.
Microsoft provides three main comparison choices for this function. vbBinaryCompare performs a binary comparison. vbTextCompare performs a textual comparison. vbDatabaseCompare is available only in Access and uses the database’s comparison rules. A fourth constant, vbUseCompareOption, tells VBA to follow the module-level Option Compare setting (Microsoft, 2022a).
The hidden trap appears when compare is omitted. Microsoft states that the module’s Option Compare setting determines the comparison. If a module does not contain that statement, the VBA default is Binary. Option Compare Text makes string comparisons case-insensitive according to the system locale, while Option Compare Database is Access-only (Microsoft, 2021).
That means two visually identical calls can behave differently if they live in modules with different comparison settings. A tutorial that says the omitted comparison is always binary is therefore incomplete: binary is the default when no other module-level setting changes it.
For code that must be easy to audit, pass the intended rule explicitly:
Dim pos As Long
pos = InStr(1, “Quarterly REFUND”, “refund”, vbTextCompare)
Here, the behavior is visible at the call site. A future editor does not have to inspect the top of the module to understand whether case matters. That small choice also makes code easier to move between Excel workbooks, Access modules, and shared utility libraries.
Locale still matters. Text comparison follows textual sort rules rather than a simple ASCII-style case fold, so international data should be tested with the actual language and symbols the workbook receives. If the task is exact machine-oriented matching, binary comparison is usually easier to reason about. If the task is human text, text comparison is often closer to user expectations.
Build Search-Then-Act Pipelines That Fail Safely
The strongest pattern is simple: search first, validate the returned position, then perform the action. Do not combine all three steps into one expression unless the input is fully controlled.
Microsoft’s Access documentation shows a practical example that finds the first period in an IP address and then uses Left to extract the first octet. The pattern works when the period exists. Production code should also decide what happens when it does not (Microsoft, n.d.).
Dim ip As String
Dim dotPos As Long
Dim firstOctet As String
ip = “10.20.30.40”
dotPos = InStr(1, ip, “.”, vbBinaryCompare)
If dotPos > 0 Then
firstOctet = Left$(ip, dotPos – 1)
Else
firstOctet = vbNullString
End If
That If statement is not decorative. Without it, a missing delimiter can push an invalid length into the extraction function. The same rule applies when parsing Region=North, file names, customer notes, or imported identifiers.
Input quality matters too. A cell may contain a worksheet error, a blank, a number formatted as text, or whitespace that changes the match. Our Excel data-cleaning guide covers the wider discipline of normalizing inputs before automation. The search function should not be expected to repair data quality problems it was never designed to solve.
A small wrapper can make intent safer
For repeated “does this text contain that phrase?” checks, a wrapper can turn the native position result into a deliberate Boolean while adding a policy for Nulls and empty search terms:
Public Function ContainsText(ByVal haystack As Variant, _
ByVal needle As Variant, _
Optional ByVal mode As VbCompareMethod = vbTextCompare) As Boolean
If IsNull(haystack) Or IsNull(needle) Then Exit Function
If Len(CStr(needle)) = 0 Then Exit Function
ContainsText = (InStr(1, CStr(haystack), CStr(needle), mode) > 0)
End Function
This wrapper intentionally changes one native behavior: an empty needle becomes False instead of returning the start position. That is not universally “more correct”; it is a business rule that prevents blank criteria from matching everything. The value comes from making that rule explicit.
Unexpected runtime failures belong in a separate error-handling layer. If a larger routine reads files, worksheets, objects, or external data, capture useful error context rather than hiding every failure with On Error Resume Next. Our Err.Description VBA guide shows how to preserve the number, readable message, and source when a macro crosses that boundary.
Choose the String Tool That Matches the Question
Text code becomes clearer when the operation matches the tool. The first-match function is excellent at locating a literal substring, but it is not a wildcard engine, a tokenizer, or a replacement function.
| Tool | Best question | Typical result | Main caution |
| InStr | “Where is the first literal match from this point?” | 1-based position or 0 | Comparison mode and empty-search behavior matter. |
| InStrRev | “Where is the last match before this point?” | 1-based position or 0 | Its argument order is not the same as the forward-search function. |
| Like | “Does this text fit a wildcard pattern?” | True, False, or Null | Pattern rules and Option Compare affect matching. |
| Split | “How do I break text into parts around a delimiter?” | Array of substrings | It is better for tokenizing than repeated manual extraction. |
| Replace | “How do I substitute matching text?” | New string | It transforms text rather than merely locating it. |
Microsoft documents InStrRev as the right-to-left counterpart and warns that its syntax is different. Microsoft’s Like operator supports wildcard patterns such as *, ?, #, and character lists, making it a better fit when “contains this exact phrase” is not the real question (Microsoft, 2022b; Microsoft, 2022c).
There is also InStrB, which returns a byte position rather than a character position. Microsoft frames it for byte data contained in a string. For ordinary worksheet text, character positions are normally easier to reason about. Byte offsets are a specialized requirement, not a faster version of normal text search (Microsoft, 2022a).
A useful design rule is to separate search from transformation. Locate with a search function, split with Split, replace with Replace, and use Like when the requirement is genuinely pattern-based. Code becomes easier to review because each operation says what it means.
Real Workflows Where the Position Changes the Action
A position is useful because it becomes a boundary. In workbook automation, that boundary often drives the next step.
One common pattern is worksheet classification. If a sheet name contains “Month”, code may hide, export, or format that sheet. ExcelMojo and WallStreetMojo both use worksheet-name examples, showing how the function becomes a filter inside a For Each loop rather than an isolated string exercise (ExcelMojo, 2026; WallStreetMojo, 2025).
Another pattern is text routing. Cuzick’s 2025 Zero To Mastery tutorial uses customer-service messages as a teaching case: scan subject or body text for terms such as “refund” and send matching items into a dedicated workflow. The implementation is simple, but the broader lesson is useful. Search logic becomes operational logic once a match changes where data goes (Cuzick, 2025).
A third pattern is boundary extraction. Find = in Region=North, then take the text after it. Find the first period in an IP address, then take the text before it. Find the last period in a file name with the reverse-search function, then treat what follows as an extension. These are all safer when the delimiter is checked before extraction.
In larger Excel routines, text search may sit beside recalculation, data refresh, or output generation. Keep those stages separate so a search bug does not look like a calculation bug. Our Application.Calculate VBA guide explains why explicit boundaries also matter when macros change workbook state and then rely on calculated results.
A Test Matrix That Catches the Quiet Bugs
A few small tests cover most production failures. Run them against the exact comparison mode your project uses.
| Test case | Expected behavior | What it protects against |
| Exact match at first character | Returns 1 | Confirms 1-based indexing. |
| Target absent | Returns 0 | Prevents unsafe extraction or false positives. |
| Case differs under binary compare | Usually returns 0 | Confirms case-sensitive logic. |
| Case differs under text compare | Match position returned | Confirms case-insensitive intent. |
| Empty target string | Returns the chosen start position | Exposes blank criteria before they match unintentionally. |
| Null source or target | Returns Null | Forces a policy for Variant data. |
| Start begins after an earlier match | Returns a later absolute position | Confirms that numbering does not restart at start. |
| Start is beyond the source length | Returns 0 | Catches bad calculated offsets. |
Add project-specific cases after these. If you parse names, test single-word names, repeated spaces, leading spaces, and blanks. If you scan logs, test prefixes, mixed case, and lines with no delimiter. If you search imported IDs, test text/number coercion and hidden whitespace.
The main information gain is procedural: the function itself is small, but the contract around it should be tested like any other parser. A one-line search becomes reliable only when its inputs, comparison mode, and failure path are known.
The Future of VBA Text Search in 2027
The function is unlikely to be the part of Office automation that changes most in 2027. The bigger shift is the environment around it. Microsoft’s February 2026 Office Scripts documentation continues to position scripts as reusable Excel automation that can run across supported clients and connect with Power Automate. Microsoft’s comparison page still describes VBA as desktop-focused, with broader desktop feature coverage, while Office Scripts target secure, cross-platform and cloud workflows (Microsoft, 2026; Microsoft, 2023).
That suggests coexistence rather than a clean replacement. Existing macro-heavy workbooks, Access databases, desktop events, COM automation, and cross-Office workflows still depend on VBA. New cloud-first Excel workflows may increasingly use TypeScript-based Office Scripts.
The migration risk is semantic, not just syntactic. JavaScript’s String.indexOf() is zero-based and returns -1 when no match exists, while the VBA function is 1-based and returns 0 when a non-empty target is not found. Both also have special behavior for an empty search string (MDN Contributors, 2025; Microsoft, 2022a). Copying logic without rewriting the tests can create an off-by-one error or invert a “not found” branch.
Teams planning for 2027 should document three things now: indexing convention, not-found value, and case policy. Those rules travel better than any single line of code.
Key Takeaways
- Treat the returned number as a position contract, not as a vague truthy value.
- Store positions in Long so the variable matches Microsoft’s documented return type.
- Pass an explicit comparison mode when case behavior affects correctness or portability.
- Guard against 0, Null, and an empty search target before extracting or changing text.
- Use the reverse-search function for last-match problems and Like for wildcard patterns.
- Test indexing and “not found” semantics again when moving logic from VBA to JavaScript-based Office Scripts.
Conclusion
InStr is small enough to learn in minutes, but reliable use depends on choices that sit around the call: the data type that stores the result, the comparison rule, the meaning of an empty search term, and the action taken when no delimiter is present. Those details are why production code should look slightly more deliberate than a tutorial one-liner.
The safest pattern is consistent. Normalize or validate the input, search with an explicit comparison mode when it matters, store the position in a Long, test the result, and only then extract, format, route, or classify data. When the real task is a last-match search, wildcard pattern, split, or replacement, use the tool designed for that job instead of stretching one function too far.
For 2027, the practical question is not whether desktop VBA disappears overnight. It is whether teams can preserve clear text-search rules as automation expands into Office Scripts and cloud workflows. Code changes. Indexing contracts and test discipline should remain explicit.
FAQ
What does InStr return in VBA?
It returns a Variant containing a Long that represents the 1-based position of the first match. If a non-empty target is not found, the result is 0. If either searched string is Null, the result is Null. An empty target is a special case: it returns the start position rather than 0 (Microsoft, 2022a).
Is the function case-sensitive?
It depends on the comparison rule. vbBinaryCompare is case-sensitive in the usual sense, while vbTextCompare performs a textual comparison that is generally case-insensitive. If the comparison argument is omitted, the module’s Option Compare setting controls the behavior; without an explicit statement, VBA defaults to Binary (Microsoft, 2021; Microsoft, 2022a).
How do I check whether a string contains text?
Search for the target and test whether the returned position is greater than zero. In reusable code, also decide how you want to treat Null values and a blank target string. A Boolean wrapper can make that policy explicit, especially when the same containment test appears in many procedures.
What is the difference between InStr and InStrRev?
The forward-search function finds the first qualifying occurrence from the left, starting at the position you specify. InStrRev searches from the end toward the beginning and is useful for the last delimiter in a path, file name, or repeated phrase. Microsoft notes that the two functions use different argument order, so do not swap them mechanically (Microsoft, 2022b).
Can I use it in Microsoft Access queries?
Yes. Microsoft Support documents use inside Access expressions as well as VBA modules. Access also supports vbDatabaseCompare, which compares text using the database’s sort rules. In query expressions, guard downstream functions such as Left or Mid when the delimiter might be missing so a zero position does not feed an invalid length (Microsoft, n.d.).
Why can an empty search string return a match position?
Because Microsoft defines an empty string2 as a special case that returns start. This can be useful in low-level string logic, but it can surprise application code that treats any positive result as “the user’s term was found.” Validate blank search criteria first when an empty term should mean “do not search” (Microsoft, 2022a).
Methodology
This guide was researched against Microsoft’s official documentation for the forward-search function, Option Compare, InStrRev, Like, the VBA Integer type, Microsoft Access expressions, Office Scripts, and Microsoft’s VBA-versus-Office-Scripts comparison. We also reviewed current ranking tutorials from TrumpExcel, Zero To Mastery, AutomateExcel, WallStreetMojo, ExcelMojo, ExcelChamps, TechOnTheNet, Microsoft Support, Microsoft Learn, and Iran VBA to identify repeated structures and missing production safeguards.
The analysis is documentation-based. We did not run a live VBA interpreter or benchmark large workbooks, so performance claims are intentionally limited. Worked examples are derived from documented behavior and should still be tested in the target workbook, Access database, locale, and Office build.
This article was drafted with AI assistance and reviewed by the Perplexity AI Editorial Team. All data, citations, and claims have been independently verified against primary sources.
References
AutomateExcel. (2024, May 29). The VBA InStr function: Finding text in Excel. AutomateExcel.
Bansal, S. (2026, April 22). Excel VBA InStr function – explained with examples. TrumpExcel.
Cuzick, T. (2025, August 25). Beginner’s guide to the InStr function in VBA. Zero To Mastery.
ExcelMojo. (2026). VBA InStr Excel – syntax, step by step examples, how to use.
MDN Contributors. (2025, July 10). String.prototype.indexOf(). MDN Web Docs.
Microsoft. (2021, September 13). Option Compare statement (VBA). Microsoft Learn.
Microsoft. (2022a, March 29). InStr function (Visual Basic for Applications). Microsoft Learn.
Microsoft. (2022b, March 30). InStrRev function (Visual Basic for Applications). Microsoft Learn.
Microsoft. (2022c, March 29). Like operator. Microsoft Learn.
Microsoft. (2022d, January 21). Integer data type. Microsoft Learn.
Microsoft. (2023, April 11). Differences between Office Scripts and VBA macros. Microsoft Learn.
Microsoft. (2026, February 27). Office Scripts in Excel. Microsoft Learn.
Microsoft. (n.d.). InStr Function. Microsoft Support.
WallStreetMojo. (2025). VBA InStr – how to use Excel VBA InStr function? (Examples).
