Microsoft Office Tutorials and References

In Depth Information

**Comparing, Finding, and Measuring Text**

FIND

FIND takes three arguments:

✓
The string to find

✓
The larger string to search in

✓
The position in the larger string to start looking at; this argument is

optional

If the third argument is left out, the function starts looking at the beginning of

the larger string. Here are some examples:

Value in Cell A1

Function

Result

Happy birthday to you

=FIND(“Birthday”,A1)

#VALUE!

Happy birthday to you

=FIND(“birthday”,A1)

7

Happy birthday to you

=FIND(“y”,A1)

5

Happy birthday to you

=FIND(“y”,A1,10)

14

In the first example using FIND, an error is returned. The #VALUE! error is

returned if the text cannot be found. Birthday is not the same as birthday,

at least to the case-sensitive FIND function.

SEARCH

The SEARCH function takes the same arguments as FIND. The two common

wildcards you can use are the asterisk (*) and the question mark (?). An

asterisk tells the function to accept any number of characters (including zero

characters). A question mark tells the function to accept any single

character. It is not uncommon to see more than one question mark together as a

wildcard pattern. Table 16-3 shows several examples.

Table 16-3

Using the SEARCH Function

Value in

Cell A1

Function

Result

Comment

Happy

birthday

to you

=SEARCH

(“Birthday”,A1)

7

Birthday starts in

position 7.

Happy

birthday

to you

=SEARCH(“y??”,A1)

5

The first place where

a
y is followed by any

two characters is at

position 5. This is the

last letter in
Happy,

a space, and the first

letter in
birthday.