SEARCH Function and FIND function in Microsoft Excel

There are two very similar tasks in Excel to search for data in cells that match the parameters: SEARCH AND FIND. They are so similar in reality as to why they have two distinct functions that basically perform the same results and are identical in the construction of the formula. This article discusses one of the fundamental differences.

SEARCH Introduction

The SEARCH function can find another character or character string in another cell and return the value for the start position. In other words, if you are trying to find out where a character in the cell contains a word, phrase, or other type of information, you can use the SEARCH function. Function Format:

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

For example, if the word "alphabet" was in cell C2 and the model needed the given letter "a" then formula = SEARCH ("a", C2,1) and the result would be 1. If you continue to do this simple example, if you searched for the "b" in the word, the formula = SEARCH ("b", C2,1) and the result 6 You can also search for the string. For example, if cell F2 contains 1023- # 555-A123, formula = SEARCH ("A12", F2.1) returns 11 as a response.

FIND Introduction

FIND is another way to find a character or string in another cell, and return the value for the starting location, just like the SEARCH function. Format of the function:

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

The letter "a" of the letter C2 is detected by = FIND ("a", C2.1) and the result is: 1. With "b" the cell C2 ends = FIND ("b" , C2.1), resulting in number 6. Finally, the similarity path continues if cell F2 1023- # 555-A123 (as before) FIND ("A12", F2.1) would give the 11th response. As you can see, both methods will have the same effect.

Note: You probably quickly realized that the word in C2 is two. If the starting point is set to 1 in each formula, it adds the first copy of the letter "a". If you have to choose the following example, the "start_num" part of the formula will certainly be 2, leaving the first instance of the letter so that 5 replies are given.

Main Differences

The main difference between the SEARCH function and the FIND function is that FIND detects capital letters and not SEARCH. So if you used formula = SEARCH ("A", C2.1) (note "A" capital), then the result will still be 1 as in the previous one. If you want to use the formula = FIND ("A", C2.1), then #VALUE! FIND is case sensitive, and the word "alphabet" is not "A".

Another difference is that SEARCH allows the use of wildcards, while FIND does not. In this context, the question mark searches for an exact phrase or string in a cell, and the star searches for the beginning of the string at the beginning of the star string. For example, the formula = SEARCH ("a? P", C2.1) in our example would give answer 1, as it searches for an exact grouping of the letter "a" just after "p". Since it is at the beginning of the word, the returned value 1. With the example of the alphabet, formula = SEARCH ("h * t", C2,1) gives 4 values. In this case, the substitute A "*" may represent any number of characters between "h" and "t" until the two letters in the formula begin and end. If the formula = SEARCH ("h * q", C2.1), then #VALUE!

Briefly, these two formulas are very similar, and unless you need an exact character or string, you are likely to make a mistake on the SEARCH page. Cases where this is not possible may include specific SKUs or employee names. In my experience, LESSON has given more to specific financial modeling exercises but it helps to understand usage and profit differences as it works on its own modeling projects.

Source by Russ Steward

Leave a Reply

Your email address will not be published. Required fields are marked *