Excel VBA INSTR Function

This Excel tutorial explains how to use Excel INSTR Function for VBA, and explain difference among vbUseCompareOption, vbBinaryCompare and vbTextCompare.

Excel VBA INSTR Function

Excel INSTR function is used to search a substring (part of the string) within a string and return the position of the first occurrence.

For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function will return number “8”.

If we try to search substring “Division” within “FinanceDepartment”, since it cannot be found, number “0” will return.

INSTR is always used to test whether a string contains a substring, as always we just want to know “True” or “False” but not the actual position, so we can simply test whether the returned value is “0”.

For Excel spreadsheet, since there is no INSTR function, consider using Search Function or Find Function.

Syntax of Excel VBA INSTR Function

InStr( [start], string, substring, [compare] )

start is optional. It is the starting position for the search, default i s 1. For example, if you want to search “De” from “Development Department”, beginning from the 13th position, the function will return 13, not 1.

string is the full string

substring is part of the string you want to search

compare is optional. It is the type of comparison to perform. It can be one of the following values:

VBA Constant Value Explanation
vbUseCompareOption -1 Default value, uses setting of Option Compare statement defined at Module level.(Two options: Option Compare Text / Option Compare Binary)If you don’t write Option Compare statement, Option Compare Binary is default
vbBinaryCompare 0 Binary comparison, turn text into a sequence to compare. Since the sequence is case sensitive, you can compare text using this option if you want the comparison to be case sensitive, which means you cannot search “aaa” within “AAA”
vbTextCompare 1 Compare text, case insensitive, which means you can search “aAa” within “AAA”

Note that if you use [compare], you have to also define [start], otherwise Excel mistakenly thinks your string is start.

Excel worksheet does not have INSTR function, but FIND Function is very similar to SEARCH, but they are different in the way that FIND does not allow case sensitive comparison and return error if substring is not found.

Example of Excel VBA INSTR Function

VBA Code Result Explanation
INSTR(3,”123123″,”2″) 5 The first occurrence of “2” in “123123” starting from position 3 is at position 5
INSTR(“FINANCE”,”f”) 0 By default, comparison is case sensitive, therefore “f” cannot be found in “Finance” and return 0
INSTR(1, “FINANCE”, “f”, vbTextCompare) 1 vbTextCompare turns the comparison into case insensitive. Defining[compare] requires you to also define [start]

Alternative of Excel VBA INSTR Function – using Wildcard

If you just want to check if a text contains specific substring, you can use wildcard to return TRUE or FALSE.

VBA supports the below three Wildcard characters. Note that Wildcard is case sensitive.

Wildcard Meaning Example
* Represents one or more characters (any character) J*     any text that starts with J
*J     starts with any text but ends with J
*J*   any text that has J in the middle
? Represents one character (any character) J?     2 characters that start with J
?J     2 characters that end with J
?J?   3 characters with J in the middle
~ Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ? J~**   any text that starts with J*
~**J   any text that starts with * and ends with J
~?*~* any text that starts with ? and ends with *

The below code check if worksheet contains “Del”, if TRUE delete it.

Sub loopWS()
    Application.DisplayAlerts = False
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "*Del*" Then
            ws.Delete
        End If
    Next ws
    Application.DisplayAlerts = True
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/gg264811%28v=office.15%29.aspx

http://www.techonthenet.com/excel/formulas/instr.php

Leave a Reply

Your email address will not be published.