SEARCH Function and derive Function in Microsoft Excel

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

SEARCH Introduction

The SEARCH function is a blueprint to rep a character or string within another cell, and it will return the value associated with the starting status. 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 expend 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 dwelling of the letter "a" in that cell, you would utilize the formula =SEARCH("a",C2,1), and the result would be 1. To continue this simplistic example, if you were seeking the dwelling of "b" in the word, the formula would be =SEARCH("b",C2,1), and the result would be 6. You can also utilize 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 retort.

acquire Introduction

The net function is another intention to accept a character or string within another cell, and it will return the value associated with the starting residence, impartial like the SEARCH function. The format for this function is:

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

Using the same example as before, the dwelling 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 retort. As you can watch, up to this point, both methods would give you the same results.

Note: You probably fast 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 engage up the first instance of the letter "a". If we needed to decide the next instance, we could merely have the "start_num" fraction of the formula to be 2, thus skipping the first instance of the letter and resulting in an retort of 5.

Main Differences

The main disagreement between the SEARCH function and the earn function is that regain is case sensitive and SEARCH is not. Thus, if you ragged the formula =SEARCH("A",C2,1) (designate the capital "A"), the result would quiet be 1, as in the case before. If you were to utilize the formula =FIND("A",C2,1), you would glean #VALUE!. catch is case sensitive and there is no "A" in the word "alphabet".

Another dissimilarity is that SEARCH allows for the spend of wildcards whereas earn does not. In this context, a expect stamp will see for an loyal phrase or series of characters in a cell, and an asterisk will peek for the beginning of the series of characters accurate before the asterisk. For example, the formula =SEARCH("aallp",C2,1) in our alphabet example would yield an retort of 1, as it is looking for an accurate 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 describe 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 employ in the formula. If the formula was =SEARCH("h*q",C2,1), you would find #VALUE!.

In short, these two formulas are very similar, and unless you need confirmation of an sincere 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 lively specific SKUs or names of employees. In my experience, SEARCH has been more gracious in specific financial modeling exercises, but it is suitable to understand the differences in usage and results as you work through your occupy modeling projects.