SEARCH Function and FIND Function in Microsoft Excel
There are two very comparable capabilities in Excel to search for information within cells matching parameters that you just dictate: SEARCH and FIND. There are so comparable, the truth is, that one wonders why have two separate capabilities that carry out just about the equivalent outcomes and are equivalent within the assemble of the formulation. This text will talk about he one, fundamental distinction.
The SEARCH operate is a option to discover a character or string inside one other cell, and it’ll return the worth related to the beginning place. In different phrases, if you’re making an attempt to determine the place a personality is inside the cell that incorporates a phrase, sentence or different kind of knowledge, you could possibly use the SEARCH operate. The format for this operate is:
If, for instance, the phrase “alphabet” was in cell C2, and your mannequin wanted the placement of the letter “a” in that cell, you’ll use the formulation =SEARCH(“a”,C2,1), and the end result can be 1. To proceed this simplistic instance, when you have been looking for the placement of “b” within the phrase, the formulation can be =SEARCH(“b”,C2,1), and the end result can be 6. You may as well use search on strings of characters. If, for instance, cell F2 incorporates 1023-#555-A123, the formulation =SEARCH(“A12”,F2,1) would yield the 11 as a solution.
The FIND operate is one other option to discover a character or string inside one other cell, and it’ll return the worth related to the beginning place, similar to the SEARCH operate. The format for this operate is:
Utilizing the identical instance as earlier than, the placement of the letter “a” in cell C2 can be found utilizing =FIND(“a”,C2,1), and the end result can be 1. In search of “b” in cell C2 can be completed be =FIND(“b”,C2,1), ensuing within the quantity 6. Lastly, persevering with on the similarity path, if cell F2 incorporates 1023-#555-A123 (as earlier than), the formulation =FIND(“A12”,F2,1) would yield the 11 as a solution. As you may see, up up to now, each strategies would provide the identical outcomes.
Be aware: You in all probability rapidly acknowledged that there are two a’s within the phrase positioned in cell C2. By stating the place to begin in every of the formulation as 1, we’ll decide up the primary occasion of the letter “a”. If we wanted to decide on the subsequent occasion, we may merely have the “start_num” a part of the formulation to be 2, thus skipping the primary occasion of the letter and leading to a solution of 5.
The principle distinction between the SEARCH operate and the FIND operate is that FIND is case delicate and SEARCH just isn’t. Thus, when you used the formulation =SEARCH(“A”,C2,1) (word the capital “A”), the end result would nonetheless be 1, as within the case earlier than. In the event you have been to make use of the formulation =FIND(“A”,C2,1), you’ll get #VALUE!. FIND is case delicate and there’s no “A” within the phrase “alphabet”.
One other distinction is that SEARCH permits for the usage of wildcards whereas FIND doesn’t. On this context, a query mark will search for an actual phrase or collection of characters in a cell, and an asterisk will search for the start of the collection of characters proper earlier than the asterisk. For instance, the formulation =SEARCH(“a?p”,C2,1) in our alphabet instance would yield a solution of 1, as it’s searching for an actual grouping of the letter “a” with something subsequent to it with a “p” instantly after. As that is at first of the phrase, the worth returned is 1. Persevering with with the alphabet instance, the formulation =SEARCH(“h*t”,C2,1) would yield a worth of four. On this occasion, the wildcard “*” can characterize any variety of characters in between the “h” and the “t” so long as there’s a string starting and ending with the 2 letters you employ within the formulation. If the formulation was =SEARCH(“h*q”,C2,1), you’ll get #VALUE!.
In brief, these two formulation are very comparable, and until you want affirmation of an actual character or string of characters, you’ll seemingly err on the facet of utilizing SEARCH. Situations the place this will not be the case would possibly contain searches involving particular SKUs or names of staff. In my expertise, SEARCH has been extra useful in particular monetary modeling workouts, however it’s useful to know the variations in utilization and outcomes as you’re employed by means of your individual modeling tasks.