Excel SEARCH Function: Are You Feeling Frisky?
The Excel SEARCH Function is another Text manipulation function that will find the starting point of any character or string of text within another string of text.
Excel SEARCH Function:
Syntax of the Excel SEARCH Function:
- SEARCH(find_text, search_text ,[start_pos])
- find_text (Required) - The text that you want to search for.
- search_text (Required) - The text you want to search for the find_text in.
- start_pos (Optional) - The starting character position in the search_text argument at which you wish to start the search.
- This function allows the use of Wildcards. The ? (question mark) character can be substituted for a single character in a text string. The * (asterisk) character can be used to substitute any number of characters in a string of text. The Tilde (~) character can be used to find the Question Mark or the Asterisk, if you are actually looking for them.
- Unlike the Excel FIND Function, this function is NOT case sensitive.
- The start_pos argument must be a positive number that is not larger than the length of the search_text. If either of these criteria are not met, you may see the #VALUE! or the #NAME? errors.
- =SEARCH("o", "Hello Moto") = 5
- =SEARCH("o", "Hello Moto", 6) = 8
- =SEARCH("to", "Hello Moto") = 9
- =SEARCH("hello", "Hello hello") = 1
- =SEARCH("hello", "Hello hello", 2) = 7
- =SEARCH("G??d", "Hello There Goodbye") = 13 (Wildcards Baby!!)
- =SEARCH("t*e", "Hello There Goodbye") = 7 (Another Wildcard!)
- =SEARCH(A4, B6, G2) = Will return the position of the text in cell A4, within the text in cell B6, starting at the position in cell G2.
Why Does This Spreadsheet Look SO Familiar?!?
Okay, okay, so I cheated a little ... What's the Big Deal???
The above spreadsheet is almost an identical copy of the one I used in the Excel FIND Function. The reason for this, is that they are almost identical functions. The only real difference is that the Excel FIND Function is Case Sensitive and the Excel SEARCH Function is not. This can be very useful depending on whether you have strict requirements or not, respectively. The results from either function may be exact or they could vary slightly depending on the text you are Searching through. Play around with each of them to see which you prefer or need for your desired function.
What are Wildcards and Why Do I Need Them?
Wildcards are provided as a way to find what you are looking for when you don't necessarily know what characters might be surrounding the data you are looking for. The wildcard characters are the Question Mark (?), the Asterisk (*) and the Tilde (~).
The Question Mark (?) allows you to substitute any single character that you may not know. For instance, Dev?n will find the words Devin, Devon, Deven, Devyn, etc... You can use multiple Question Marks in a row, if you know how many characters there are.
The Asterisk (*) allows you to find any number of characters at a time. For instance, *ing will find words such as running, jumping, skiing, flying, etc. It can be used anywhere within the text. Another example would be s*r could return summer, sour, sucker, sweetener, etc.
What if you need to find an actual Question Mark or an Asterisk in the text?
Thankfully, Excel makes this easy too. If you need to find either of the two, then just precede it with the Tilde (~) character. Lets say you are looking for a part number and it has a Question Mark as part of the text, such as PN0943?1234. You might pass "~?" and it will return the value of 7, since that is the position of the Question Mark in the text. If you were to leave out the Tilde (~), it would return a value of 1, since it now assumes you meant for it too look for the first instance of any character.
Download The Example Spreadsheet Here:
Why use the Excel SEARCH Function?
The Excel SEARCH Function is useful when you need to locate the starting point of a word or a set of words, in a long string of characters. Once you have the starting point, you can use other functions to pull out the data you need. (Same as the Excel FIND Function)
The major difference is the ability to use Wildcards when you are unsure of certain letters, words or any old string of text. These can make it all worth while.
This function is a bit more flexible than the Excel FIND Function, although it can yield the same results, depending on your search text. It is really the addition of Wildcards that make this function superior. In my humble opinion ...
Share, Comment, Ask a Question or Rip Me a New One!
If you liked this article, please feel free to share it on social media and comment below if you have any suggestions, questions or corrections. Thank you for reading!
Remember to sign up to the newsletter email list, so you can catch all of the new posts and learn about upcoming topics, products and/or services.