SEARCH vs FIND Function in MS Excel
Have you been confused about when to use the SEARCH function versus the FIND function in MS Excel.
If yes, then worry not...you have come to the right place… I am going to explain the same in detail.
Though both these functions perform similar tasks, there are some key differences.
FIND function
FIND locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string.
Syntax
FIND(find_text, within_text, [start_num])
The FIND function syntax has the following arguments:
Find_text Required. The text you want to find.
Within_text Required. The text containing the text you want to find.
Start_num Optional. Specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.
Remarks
FIND is case sensitive and does not allow wildcard characters. If you don't want to do a case sensitive search or use wildcard characters, you can use SEARCH
Many times, you may want to look for text which is similar but with slight variations. For example, you may want to find all cells that contain the word Apple or apples or pineapples. That is where wildcard characters can be useful.
But as mentioned above, FIND does not allow wildcard characters. To avoid this problem, you have to use the SEARCH function, which allows wildcard characters.
As you may have guessed, SEARCH is very similar to FIND.
SEARCH function
The SEARCH function locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string.
For example, to find the position of the letter "n" in the word "printer", you can use the following function:
=SEARCH("n","printer")
This function returns 4 because "n" is the fourth character in the word "printer."
You can also search for words within other words. For example, the function
=SEARCH("base","database")
returns 5, because the word "base" begins at the fifth character of the word "database".
Syntax
SEARCH(find_text,within_text,[start_num])
The SEARCH function has the following arguments:
find_text Required. The text that you want to find.
within_text Required. The text in which you want to search for the value of the find_text argument.
start_num Optional. The character number in the within_text argument at which you want to start searching.
Remark
The SEARCH function is not case sensitive. If you want to do a case sensitive search, you can use FIND.
You can use the wildcard characters — the question mark (?) and asterisk (*) — in the find_text argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
If the value of find_text is not found, the #VALUE! error value is returned.
If the start_num argument is omitted, it is assumed to be 1.
Also, here is the book which I am writing, which teaches MS Excel in a fun way.
This covers the difference between SEARCH & FIND in more detail with various examples. This has fun puzzles and activities using Excel, which helps you to learn faster and remember for a longer time!
To learn more and see examples click here : https://support.microsoft.com/en-us/office/search-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513b495
Thus, SEARCH is very useful when you want to look for a particular text with slight variations. On the other hand, FIND is useful if you want to look for text in a particular case, as it is case-sensitive.
Hope this article helped you understand the small differences and made you comfortable in using these 2 functions at the relevant place.
PS: Are you trying to learn MS Excel in a fun way? Or, you think you know MS Excel well?
Try this puzzle now and test yourself.
https://www.mlbrains.com/learning/
It's free and a lot of fun!