Page 136 - Excel Progamming Weekend Crash Course

h540629 ch09.qxd 9/2/03 9:34 AM Page 111

Session 9 — Working with Text 111

Searching for Text

To locate an occurrence of one string within another string, use the InStr and InStrRev
functions. InStr starts looking at the beginning of the target string, while InStrRev starts
at the end. The syntax for these functions is:

InStr(start, string1, string2, compare)
InStrRev(string1, string2, start, compare)

Start. The character position at which to start the search. Optional; if omitted,
the search starts at the first (InStr) or last (InStrRev) position. Even though
InStrRev searches from the end of the string, it interprets the start argument as
a position relative to the start of the string.
String1. The string being searched.
String2. The target string (the string being searched for).
Compare. A constant specifying how string comparisons are performed. See Table
9-2 for permitted settings for this argument. Optional. If omitted, a binary (case-
sensitive) comparison is performed.

Table 9-2 Constants for the InStr and InStrRev Functions’ Compare Argument
Constant (value) Meaning
vbUseCompareOption (-1) The setting of the Option Compare statement is used.
vbBinaryCompare (0) A binary (case-sensitive) comparison is performed.

vbTextCompare (2) A textual (noncase-sensitive) comparison is performed.

Use the Len function, covered later in this session, to determine the length
of a string.
Cross-Ref
The functions return the character position at which string2 is found within string1,
or 0 if it is not found. Character positions are always counted from the start of the string,
with 1 being the first character. They also return 0 if either string is zero length or if start
is greater than the length of string1.

The Option Compare statement specifies how text comparisons are per-
formed within a VBA module. Place the statement at the start of a module,
Note outside of any procedures. Use Optional Compare Text for case-insensitive
comparisons, or Option Compare Binary for case-sensitive comparisons.
Table 9-3 shows some examples of using the InStr and InStrRev functions.