Excel Find and Search Functions
There are many functions in Excel that help users perform different kinds of functionalities easily and efficiently. Functions are categorized into different groups such as text, logical, financial, date and time, mathematical etc. Though Excel is mainly intended for simplifying mathematical calculations, there are many situations where the text data need to be manipulated. Excel text functions help to manipulate text data. Two main Excel text functions are FIND and SEARCH. The basic functionality achieved by these two functions is same: finding the starting position of a substring within another string. But, there are two major differences between these two. First of all, FIND is case sensitive and SEARCH is not case sensitive. Secondly, SEARCH allows searching using wildcard characters “?” and “*”, whereas FIND does not.
The syntax of FIND function is FIND (substring, string, [starting-position]) and the syntax of SEARCH function is SEARCH (substring, string, [starting-position]). Here substring is the string you need to search and string is the main string to search within. The starting-position is the position at which the search will start and this parameter is optional with default value 1. For example, if you wish to find the location of “cat” in the string “concatenation”, you should write the FIND function as FIND(“cat”,”concatenation”) and the result would be 4. Similarly, if you use SEARCH function, your function would be SEARCH(“cat”,”concatenation”) and the result would be 4. Here, the first letter of substring “cat” is at the 4th position in string “concatenation”. If you find the position of string “ten” within “concatenation”, then the result would be 6.
FIND function is case sensitive. In the above example, if you want to find the position of “cat”, you should search using “cat” itself. If you search the string using “Cat” or “cAt”, you will not get the expected result. But, if you are using SEARCH function, you would get the result 4 even if you use “Cat” or “cAt”. Moreover, if you wish to find the string that starts with “n” and ends with “n” with more than one character in between you can use the SEARCH function with parameters as SEARCH (“n*n”,”concatenation”) and the result would be 3 as the string found “ncaten” is at 3rd position. Similarly, if you wish to find a string that starts with “t” and ends with “n” with a single character in between, then you can write the SEARCH function as SEARCH (“t?n”,”concatenation”) and the result would be6 as the string “ten” is at 6th position. But searching using wildcard characters is not possible with FIND function. If the FIND function and SEARCH function do not find a matching value, it would return a #VALUE! error.
To understand both these functions in details, open a new Excel file and enter the details as shown below.
Now, click on cell C1 and to insert a FIND function, go to FormulasTextFIND as shown below.
Now you will get a screen as follows.
Now, the cursor would be in the Find_text field and click on the cell B1 as it contains the substring we need to search. Then, click in the Within_text field and click on the cell A1 as it contains the string within we need to search. Now, the Function Arguments window will have values as shown below.
The result is already there on the window as “Formula result = 4”. Click on OK button. Now, you will have the value 4 in the cell C1 as the starting position of “cat” within the string “concatenation” is 4. If you just copy this formula in cells C2 to C13, you will get the result as follows.
Here, you could find that many cells show error #VALUE!. The reason is that it could not find a matching string. For example, FIND function could not find a string “Tin” within “CONTINUE” as this function is case sensitive. Similarly, it could not find the strings “n*n” or “t?n” within the string “Concatenation” as FIND function looks for an exact match.
Now, let’s repeat the process for SEARCH function. Click on cell D1 and, go to FormulasTextSEARCH. Enter the parameters as shown below and click OK button.
Copy this formula in cells D2 to D13, you will get the result as follows.
Now, you can be happy as most of the results are without errors. But, if you check the result in D9, you will find that it is not the correct answer. The “?” is at 12th position as shown in C9. Then, why is it displayed as 1? The reason is SEARCH function misunderstands “?” as a wildcard character. So, if you really want to search “?” or “*”, you should insert “~” before the character. Thus if you search using SEARCH(“~?”, “How are you?”), then your result would be 12. If you check D11, you could find that the error is because that wildcard character “?” represents single character and there is no substring that starts with “y” and ends with “r” with a single character in between.
Read More about Excel here