SEARCH Function and FIND Function in Microsoft Excel

There are two very similar functions in Excel to look for data inside of cells matching parameters that you dictate: SEARCH and FIND. There are so similar, in fact, that one wonders why have two separate functions that perform virtually the identical results and are identical in the construct of the formula. This article will discuss he one, basic difference.

SEARCH Introduction

The SEARCH function is a way to find a character or string within another cell, and it will return the value associated with the starting place. In other words, if you are trying to figure out where a character is within the cell that contains a word, sentence or other type of information, you could use the SEARCH function. The format for this function is:

=SEARCH("find_text","within_text",start_num).

If, for example, the word "alphabet" was in cell C2, and your model needed the location of the letter "a" in that cell, you would use the formula =SEARCH("a",C2,1), and the result would be 1. To continue this simplistic example, if you were seeking the location of "b" in the word, the formula would be =SEARCH("b",C2,1), and the result would be 6. You can also use search on strings of characters. If, for example, cell F2 contains 1023-#555-A123, the formula =SEARCH("A12",F2,1) would yield the 11 as an answer.

FIND Introduction

The FIND function is another way to find a character or string within another cell, and it will return the value associated with the starting place, just like the SEARCH function. The format for this function is:

=FIND("find_text","within_text",start_num).

Using the same example as before, the location of the letter "a" in cell C2 would be discovered using =FIND("a",C2,1), and the result would be 1. Looking for "b" in cell C2 would be accomplished be =FIND("b",C2,1), resulting in the number 6. Finally, continuing on the similarity path, if cell F2 contains 1023-#555-A123 (as before), the formula =FIND("A12",F2,1) would yield the 11 as an answer. As you can see, up to this point, both methods would give you the same results.

Note: You probably quickly recognized that there are two a's in the word located in cell C2. By stating the starting point in each of the formulas as 1, we will pick up the first instance of the letter "a". If we needed to choose the next instance, we could merely have the "start_num" part of the formula to be 2, thus skipping the first instance of the letter and resulting in an answer of 5.

Main Differences

The main difference between the SEARCH function and the FIND function is that FIND is case sensitive and SEARCH is not. Thus, if you used the formula =SEARCH("A",C2,1) (note the capital "A"), the result would still be 1, as in the case before. If you were to use the formula =FIND("A",C2,1), you would get #VALUE!. FIND is case sensitive and there is no "A" in the word "alphabet".

Another difference is that SEARCH allows for the use of wildcards whereas FIND does not. In this context, a question mark will look for an exact phrase or series of characters in a cell, and an asterisk will look for the beginning of the series of characters right before the asterisk. For example, the formula =SEARCH("aallp",C2,1) in our alphabet example would yield an answer of 1, as it is looking for an exact grouping of the letter "a" with anything next to it with a "p" immediately after. As this is in the beginning of the word, the value returned is 1. Continuing with the alphabet example, the formula =SEARCH("h*t",C2,1) would yield a value of 4. In this instance, the wildcard "*" can represent any number of characters in between the "h" and the "t" as long as there is a string beginning and ending with the two letters you use in the formula. If the formula was =SEARCH("h*q",C2,1), you would get #VALUE!.

In short, these two formulas are very similar, and unless you need confirmation of an exact character or string of characters, you would likely err on the side of using SEARCH. Instances where this may not be the case might involve searches involving specific SKUs or names of employees. In my experience, SEARCH has been more helpful in specific financial modeling exercises, but it is helpful to understand the differences in usage and results as you work through your own modeling projects.

Why it is More Difficult to Search in Excel Than in Word

Excel, unlike Word, has sheets that you can work with simultaneously. These sheets can be found right at the bottom of the Excel window shown as tabs. And just by looking at its appearance, seeing the cells can definitely make you say that to search in Excel files should be a more complicated process than to search in Word files.

Doing a search within a Word document is easy. There are two ways to do the search in Word documents. First is by accessing the Edit menu then clicking on Find. And the other way is through its shortcut counterpart. Just hit the Ctrl key together with the F key in your keyboard and you will pull up a box with a space for you to type in the words you want to search. If your Word document has multiple pages, you can position your cursor where your document starts and do the search from there and it will search the document, from page one, all the way to your last page.

The Ctrl+F can still work in Excel; however, it is a little tricky. Excel documents do not have pages; they have sheets instead. If you simply press Ctrl+F, it will only do a search on the current sheet that you are on. You won't include the other sheets in the search that you want to do for the entire document. Although manually doing the search for each sheet is still possible, and it wouldn't be too tedious, especially if you only have few sheets to work on. However, there is an even easier way to do the search.

These are the steps that you can do to make things easier if you want to find certain words or strings in all the sheets of your Excel document:

First is to right click any sheet that you have in your Excel document. The right click will bring out a menu. And from the said menu, click "Select All Sheets". Clicking on "Select All Sheets" puts your Excel document into Group Mode. You will know that you are already in Group Mode through two signs Excel is showing. The first sign is by looking at the tabs. The tabs are now white and the current active tab that you are in will be bolded. The second sign is by looking at the title bar. You will see the word "Group" written on the title bar.

Once in group mode, the Ctrl+F function will now work on all sheets that you have in the Excel document. Searching through all your sheets can be done in two ways. One way is by keeping the Find box open and just hit "Next" to find words. The other way is by closing the Find box and pressing Shift then F4 to find the words you are looking for.

After the search in Excel, it is important to remember to ungroup your Excel. By not doing so, whenever you work in future, all the edits that you do on a single sheet will also happen in the rest of your sheets available in the document.