Vinicius writes to ask:
"Dear Mr. Pig. I would like to know what the difference is between Excel's FIND and SEARCH. Which one should I use?"
I love the respect Vinicius shows by calling me Mr. Pig. Anyway, here is your answer Mr. Vinicius:
There are two differences between Excel's SEARCH and FIND functions.
First, the FIND function is case sensitive, while the SEARCH function is not.
As you can see in this example, the formula in D2 returns 13 because the FIND function is looking for the first Capital B. The first capital B just happens to be the 13th character.
The formula in cell D3 is using the SEARCH function. That function isn't case sensitivity, so it returns the first B it finds (character number 3).
The Second difference between FIND and SEARCH is the ability to use Wildcard characters. The SEARCH function handles wildcard characters, while the FIND function does not.
In this example, I'm using the (?) wildcard character to tell the SEARCH function to find the first string that starts with a B and ends with a C. The first string that meets my criteria starts on character 13, so that's what I get.
Which one should you use?
Technically, the SEARCH function is safer to use because you won't accidentally pull the wrong position due case sensitivity.
I don't think I've ever needed to do a case sensitive find. I'll never use the SEARCH function's wildcard ability, but it's nice that it's there.
I'll be honest; prior to this blog post, I used the FIND function mainly out of habit. In fact, prior to Vinicius' email, I kind of forgot about the SEARCH function.
I'm going to make an effort to use the SEARCH function from now on. Well Vinicius, I guess I'm recommending the SEARCH function.
