What is the Excel SEARCH Function?
The excel SEARCH function is looking for a text in another text. The SEARCH function does a case-insensitive search and allows the usage of the wild card.
Excel has other functions like the SEARCH function that is excel FIND function. Read the following article to find out what is the difference between the two.
SEARCH Syntax
SEARCH(find_text,within_text,[start_num])
find_text, required, text that you want to search.
within_text, required, text in which you want to search for the find_text.
start_num, optional, search starting position, the default is 1.
How to Use SEARCH Function in Excel
The following is an example of the SEARCH function usage and the results.
SEARCH Function #1
=SEARCH(A2,B2)
The start_num argument is optional, you can ignore it, by default is 1. The “Soft” text is in the 6th position in the “Microsoft Excel” text.
SEARCH Function #2
=SEARCH(A3,B3,C3)
You got the same result as SEARCH function #1. SEARCH functions do not distinguish uppercase or lowercase letters. SEARCH functions work in a case-insensitive manner.
SEARCH Function #3
=SEARCH(A4,B4,C4)
There is no “hard” text in the “Microsoft Excel” text. The result is #VALUE! error.
To prevent the #VALUE! error from appearing, you can use the IFERROR function. The IFERROR function prevents errors by returning the other value, for example, a zero number or “No data found” text.
SEARCH Function #4
=SEARCH(A5,B5,C5)
SEARCH functions can search by using a wildcard. “?8” means looking for number 8 that has one character before, whatever the character, could be letters, numbers or punctuation.
The result is 11, at that position, there is number 7, a character before number 8.
SEARCH Function #5
=SEARCH(A6,B6,C6)
Why does the wildcard “*8” return a different result from the wildcard “?8”?
Wildcard “?” requires a character representing the wildcard “?”, Whatever the character is. While the wildcard “*” can represent one character, two characters or even no characters at all.
In position 1, there is number 8 without any previous characters. Isn’t the number 8 at position 12 included in the search criteria? That’s right, but the SEARCH function returns only for the first occurrence.
SEARCH Function #6
=SEARCH(A7,B7,C7)
There are two C letters in the “Microsoft Excel” text. The SEARCH function returns the first C letter position, the 3rd letter.
SEARCH Function #7
=SEARCH(A8,B8,C8)
The SEARCH function returns a #VALUE! error, because the start_num argument contains zero number. You got the same error if you put a negative number on the start_num argument.
SEARCH Function #8
=SEARCH(A9,B9,C9)
#VALUE! error appears if the start_num argument is greater than the length of the text.
No need for IFERROR function in SEARCH functions #7 and #8. An error occurred because of a formula writing error. Put the correct number on start_num argument or ignore it, you will get the correct result.
