Today I want to examine the Search function and the
differences between the functions Search and Find.
On first glance they appear to do the same job. Both
allow you to find a string within a sub string. Okay now that one sentence has
caused many people to just click off this blog. But for those few go getters who
have stayed, let me demonstrate the differences with an example.
The syntax for the SEARCH function is:
SEARCH( substring, string,
[start_position] )substring is the substring that you want to find.
string is the string to
search within.
start_position is optional.
It is the position in string where the search will start. The first position is
1.
Let’s look at the following sentence…
The blue chicken has the
best food in france.
(Now
that’s a new search result for Google to find!)
If
I wanted to find the starting position of the word france (18 character in the
sentence) I could use either =Search or =Find.
A
|
B
|
|
1
|
The blue chicken has
the best food in france.
|
|
2
|
||
3
|
Result
|
Formula
|
4
|
18
|
=SEARCH("France",A1,1)
|
5
|
#VALUE!
|
=FIND("France",A1,1)
|
6
|
18
|
=SEARCH("fra*",A1,1)
|
7
|
#VALUE!
|
=FIND("fra*",A1,1)
|
Ah Ha!!! You exclaim, you forgot to capitalize the word France
in your sentence (the spelling police are everywhere). You are correct, and yet
the =Search function found the word while the Find function returned an error.
This is because the FIND function is case sensitive,
while the SEARCH function is not. FIND looks for the exact match whereas SEARCH
will just look for any match (uppercase or lowercase or mixture). And in my
formulas I capitalized the letter F in France.
The Second difference between FIND and SEARCH is the
ability to use Wildcard characters. The SEARCH function allows wildcard
characters, while the FIND function does not.
Notice the wildcard in my third example =SEARCH("fre*",A1,1
You
can use the ? and * wildcard characters; use ~? And ~* to find the ? and *
characters.
So right about now you
are asking yourself, why would I ever use the find function when search seems
so much more useful? Well the answer is case sensitivity. If you need case sensitivity, then you need to use
the FIND function.
So
which do you prefer, Find or Search?
