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.
RELATED STUFF
- Using Arrows to Edit a Named Range
- Outta My Way Formula Thingy!
- Find and Highlight Specific Values
- Creating Subtotals on Many Columns
- Protect Worksheet Structure with Array Formulas
- Copying VLOOKUPs Across Multiple Columns
- Flipping a Range Upside Down
- Paste Special Skip Blanks
- Avoiding Data Cut-Off in Excel
- Excel Defunct Defaults
Would I be correct to assume that SEARCH uses text-base string comparison, and FIND uses binary comparison? If so, one might suggest that FIND is the more efficient of the two, although I’m sure the performance benefits won’t even be noticeable.