There are some things in life that just seem to randomly disappear from your sight right when you need them the most, like that matching sock in the morning, your keys when you’re in a hurry, or a piece of data from an Excel worksheet when you’re trying to get something done. While this post won’t be of much help as to how to find your missing items faster, it will, however, teach you how to find the string of data you’re looking for in Excel, using a very simple tool – the SEARCH function.
Even though functions can seem a bit scary at first, they are among the key factors that make Excel so powerful. If you’re fairly new to Microsoft Excel or just not aware of what the software can really do, this online course on the basics of Excel is a good place to get a solid glimpse of its capabilities. Ready to break into functions? Let’s do it!
SEARCH Function Definition and Synthax
The SEARCH function, as it name suggests, is used to search for a character or a string of text within a cell. What’s interesting about this functions is the way it displays the result: instead of highlighting the character or piece of text you are looking for, as you would normally expect such a function to do, it displays the number of the character that serves as a start position for the search term within the searched cell. It sounds a bit twisted and complicated at first, but it is really simple actually; check out the example below to get a better image of how the SEARCH function works.
In this example, the search term was the letter a, and the result was 9 because a was the 9th character in that string of text.
The syntax of the SEARCH function is as follows: =SEARCH(search_term, search_range, [start_position]) , where search_term refers to the term you want to look for, search_range defines the cell of group of cell to look in and start_position, which is an optional argument, defines whether the SEARCH function should start the count from the first character in the search cell (the default option that applies if start_position is not present) or only look for the search term beyond a certain number of characters.
In this example, the search term was the letter t; as you can see, the start position was set to 4, meaning that the search function would overlook the first character, which was also a t, and only start the search from the fourth character, thus the result being 11. An important detail to remember is that, when defining a certain starting position, that value only acts as an indicator for where the searching process would start, but not also for the counting process, which still starts from the first character in the cell.
If the search term you are looking for is not present in the defined cell, the result returned by the SEARCH function will be #VALUE.
Properties of the SEARCH Function
In order to effectively use the SEARCH function, there are some things you should know about it first:
- The term you are looking for can be a simple character, a string of text of a reference to another cell. If your search term is a character or a string of text, you will need to put it between quotation marks;
- Capitalization is not taken into consideration by the SEARCH function;
- The SEARCH function can use wildcards, so if you want your search to include certain variations of your search term, you can replace a character of the search term with a ? and have the search process return a result with all the possible combinations of the word (for example using f?x as a search term will match terms such as fox, fax, fix, etc.), while using the * wildcard will replace multiple characters in your search term (searching for f*x would return all the results in the previous example, but also terms that contain a different number of letters, as long as they start with f and end in x – fedex, flux, etc.).
If you need to search for a term that’s case-sensitive, use the FIND function instead of the SEARCH function. Except for the case-sensitive aspect and the fact that FIND cannot use wildcards, the two functions are identical. To learn more about functions and which ones to use in a certain scenario, check out this in-depth online course on Excel functions and formulas.
SEARCH Function Usage Scenario
There are a lot of scenarios that can make use of the SEARCH function; however, most of them don’t use the function alone, but in combination with other functions and formulas. You can learn how to create such combinations yourself and use Excel like a pro in just two hours by taking this online course.
Here is a scenario of how to put the SEARCH function to work right away:
You have a list of people that applied for a position at your company. Your task is to filter out those people, based on their skills, and only call in for an interview the people that have a specific skill. For this example, the skill you will be looking for will be Excel.
Start by applying the SEARCH function on column B, which contains the skills of the applicants, to quickly determine who has Excel skills and who doesn’t.
Using the ISNUMBER function, you can easily translate the results of the SEARCH function into simple TRUE/FALSE values.
Now that we have TRUE and FALSE values, you can easily create custom tags using the IF function.
The end result is that we now have Accepted or Rejected labels assigned to each applicant, based on whether they have Excel skills or not. You can even combine all the functions into one single formula, to make everything look cleaner.