In Excel: InStr(1, haystack, needle, vbTextCompare) returns the character position of the first match, or 0 when the text is not found.
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. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. 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 tool above 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. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. Treat “excel vba instr” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
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.