Comparing Search function vs Find Command differences in Excel

Excel provides a wide variety of text and numeric data processing tools. One of the most popular is the SEARCH function. It allows you to determine the position of the desired alphabetic or numeric combination in a string, a cell with text information, and write it down using numbers.



Examples of using SEARCH function in Excel

To find the position of a text string in another similar one, SEARCH and SEARCHB are used. The calculation is conducted from the first character of the analyzed cell. So, if we set the SEARCH function “n” for the word “orange”, we will get the value 4, since this is exactly what the given letter stands for in the text expression.

SEARCH function works not only for finding the position of individual letters in the text, but also for the whole combination. For example, specifying this command for the words “book”, “notebook”, we will get the value 5, since the search word “book” begins with this character.

Use the SEARCH function along with such as:

• FIND (finding are case sensitive);
• MID (returns middle string);
• REPLACE (replaces characters).

It is important to remember that the SEARCH command in question is case-insensitive. If we use it to search for the position of the letter “a” in the word “Alexander”, the expression 1 will appear in the cell, since this is the first character in the analyzed information. When you specify the FIND "a" command in the same segment of the text, we get the value 6, since exactly 6 position is occupied by the lower-case "a" in the word "Alexander".

In addition, the SEARCH function does not work for all languages. It differs from the SEARCHB command in that it counts 1 byte for each character, while SEARCHB – two.

To use the function, you must enter the following formula:

=SEARCH(find_text,within_text,[start_num])

In this formula, the assigned values are defined as follows.

1. Find text. This is a numeric and alphabetic combination whose position you want to find.
2. Within text. This is the piece of text information from which you want to isolate the desired letter or combination and return the position.
3. [Start number] – This fragment is optional. But, if you want to find, for example, the letter "a" in the line with the value "А015487.Men's clothing", you must specify at the end of formula 8, so that the analysis of this fragment was carried out from the eighth position, that is, after the article. If this argument is not specified, then it is assumed to be equal to 1. If you specify the initial position, the position of the desired fragment will still be counted from the first character, even if the initial 8 were omitted in the analysis. That is, in this example, the letter “a” in the line “А015487.Men's clothing” will be assigned the value 14.

When working with the "find_text" argument, you can use the following wildcards.

1. Question mark (?). It will match any sign.
2. Asterisk (*). This symbol will match any combination of characters.

If you want to find similar characters in the string, then in the argument "find_text" in front of them you need to put a tilde (~).

If the search text was not found by the application or the initial position is set to less than 0, more than the total number of characters present, the error #VALUE! is displayed in the cell.

If "find_text" is not found, the value of the #VALUE! error is returned.



Example of using the SEARCH and MID function in Excel

Example 1. There is a set of text information with customer contact information and their names. Information recorded in different formats. It is necessary to find out from which symbol the telephone number begins.

Let's enter the source data in the table:

In the cell that will take into account the data of customers without a phone, enter the following formula:

=SEARCH(", phone",address of the cell being analyzed).

Press Enter to display the required information:

Further, we can use any other functions to display the presented information in a convenient format:

The figure shows how using the formula of the two functions MID and SEARCH we cut a piece of text from lines of different lengths. Moreover, we separate the text fragment in the right place so as to separate it from the phone number.

Sample formula SEARCH and REPLACE

Example 2. There is a table with text information in which the word “margin” needs to be replaced with “volume”.

Open an Excel workbook with processed data. Let's write a formula for finding the right word "margin":

Now we add the formula function REPLACE:

How does SEARCH function differ from FIND function in Excel?

Example 3.SEARCH function is very similar to FIND function by the principle of operation. Moreover, they actually have the same arguments. Only the names of the arguments are different, but in fact the types of values are the same:

But an experienced Excel user knows that the difference between these two functions is very significant.

Difference number 1. Sensitivity to upper and lower case (capital and small letters). FIND function is case sensitive. For example, there is a list of stock items with an article number. It is necessary to find the position of the small letter "d".

Now look at how these two functions behave differently when searching for the capital letter “D” in the search criteria:

Difference number 2. In the first “find_text” argument for the SEARCH function, we can use wildcards to indicate not an exact value, but an approximate value that should be contained in the source string. The second function FIND does not know how to use text mask substitution characters in the operation: “*”; "?"; "~".

For example, let's try to find an approximate text in the same source lines of the “names” column. To do this, we indicate the following type of search criteria using wildcard characters: "m*n".

Download examples SEARCH and FIND functions in Excel

As we see in the second difference , the FIND function is completely unable to work and recognize special characters for substituting text in the search criteria when there is an inaccurate match in the source line.