In Excel: InStr(1, haystack, needle, vbTextCompare) returns the character position of the first match, or 0 when the text is not found. Excel for the web cannot create, run, or edit VBA macros. Open the workbook in the Excel desktop app to work with macros.
On this page8
VBA macro: InStr — Find Text Inside a String
Sub FindTextPosition()
Dim ws As Worksheet
Dim txt As String
Dim pos As Long
Set ws = ActiveSheet
txt = CStr(ws.Range("A1").Value)
pos = InStr(1, txt, "excel", vbTextCompare)
If pos > 0 Then
MsgBox "Match starts at character " & pos & ".", vbInformation
Else
MsgBox "No match in A1.", vbExclamation
End If
End SubThe four-argument form is worth using by default: the leading 1 is the start position, and vbTextCompare makes the search case-insensitive. Omit that last argument and the comparison follows Option Compare, which is case-sensitive unless the module says otherwise.
How to run this macro
- Press Alt + F11 to open the VBA editor.
- Insert > Module.
- Paste the code above.
- Press
F5, or close the editor and run it from Developer > Macros. - Save the file as .xlsm so the macro is kept.
Call pos = InStr(1, textToSearch, textToFind, vbTextCompare) and store the result in a Long.
Test If pos > 0 Then before using it — 0 means not found, and passing it to Left or Mid causes a runtime error.
Use the position with Left, Mid or Right to extract the part of the string you want.
Switch to InStrRev when you need the last occurrence rather than the first.
Loop by passing pos + 1 as the new start argument to walk every occurrence in turn.
What this does
InStr searches one string for another and returns the position where it starts, counting from 1. A return of 0 means no match — there is no error and no special value, which is why every use of InStr is really a comparison against 0. The signature is InStr([start], string1, string2, [compare]). Passing the start position explicitly is what makes the fourth argument available, and that argument is the one that matters: vbTextCompare makes the search case-insensitive, vbBinaryCompare (the default) does not. Its mirror image is InStrRev, which searches from the right and is how you find the last backslash in a file path or the final dot in a filename. InStr pairs naturally with Mid, Left and Right: find the position, then slice around it. Note that VBA's InStr returns 0 for no match, whereas the worksheet FIND function raises #VALUE! — code ported from formulas often carries that assumption across incorrectly. Before the code does anything, check the environment it needs: desktop Excel, a macro-enabled workbook (.xlsm) and macros allowed for this file. If the workbook came by email or download, Office can block its macros until the file is trusted. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel vba instr”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the outcome you expected before you touch the real workbook. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Cell A1 holds "Quarterly Excel Report". Sub FindTextPosition() searches it for "excel" with vbTextCompare and reports position 11, matching despite the different capitalisation. Drop the vbTextCompare argument and the same call returns 0, which is the single most common surprise with this function. A practical use is splitting a full name: p = InStr(1, fullName, " ") gives the position of the space, then Left(fullName, p - 1) is the first name and Mid(fullName, p + 1) the last — guarded by If p > 0 Then, because a name with no space would make Left receive -1 and raise an error. To pull the extension off a filename, InStrRev(fileName, ".") finds the final dot rather than the first, which matters for "report.v2.xlsx". InStr is the foundation under almost every text-handling macro — splitting names, parsing paths, filtering rows that contain a keyword, validating that an entry has an @ in it. Learning its two quirks, 1-based positions and 0 for not-found, removes most of the runtime errors that string code produces. Run a new macro on a copy of the workbook first. Do not count on Undo to reverse what a macro changed; the saved copy is your way back.
In Google Sheets
Google Sheets does not run VBA. A macro written for Excel has to be rewritten in Google Apps Script (Extensions ▸ Apps Script), and an .xlsm file converted to Sheets keeps its cells but drops the code. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. Treat “excel vba instr” as code you are responsible for: know what it changes, keep a copy of the workbook before running it and keep the file in a macro-enabled format. In Google Sheets and Excel for the web this code does not run; those need a different mechanism.
Common mistakes
- Omitting the start argument and then wondering why the search is case-sensitive. Without the leading 1, the compare argument cannot be supplied and VBA falls back to Option Compare Binary.
- Using the result without checking it. When nothing matches, InStr returns 0, and Left(s, 0 - 1) raises "Invalid procedure call" — the not-found branch is not optional.
- Expecting a
#VALUE!-style failure because the worksheet FIND function behaves that way. InStr never errors on a miss; it returns 0, so the guard must be written by hand. - Counting from 0. VBA strings are 1-based, so the first character is position 1, and off-by-one errors when slicing come from assuming otherwise.
- Reaching for InStr to test membership in a list. InStr(1, "apple,grape", "grape") is True but so is a search for "rap" — split on the delimiter, or compare against delimited boundaries.
Frequently asked questions
How do I make InStr ignore case?
Pass vbTextCompare as the fourth argument, which requires supplying the start position: InStr(1, a, b, vbTextCompare). Alternatively put Option Compare Text at the top of the module to change the default for the whole module.
What does InStr return when the text is not found?
Zero. Not an error, not Null — just 0, so always test If pos > 0 Then before using the result in Left, Mid or Right.
How do I find the last occurrence instead of the first?
InStrRev(haystack, needle) searches from the right. It is the standard way to isolate a file extension or the final folder in a path.
How do I find every occurrence, not just one?
Loop: keep calling InStr with the start argument set to the previous result plus one, until it returns 0, collecting the positions as you go.