Excel Function: FIND vs SEARCH (A Comparative Analysis)

In Microsoft Excel, the FIND function and SEARCH function enable finding the necessary alphabetic or numeric conjunction in a string, a cell with text data. In this article, we will see an overview of the FIND and SEARCH functions in Excel.


Download Practice Workbook

You can download the workbook and practice with them.


Introduction to the FIND Function in Excel

In Excel, the FIND function is a built-in function that is classified as a String/Text Function.  The FIND function is accustomed to finding the location of a certain character or substring inside a text string.

Syntax:

The syntax for the FIND function is:

FIND(find_text, within_text, [start_num])

Arguments:

find_text:  [required] The text we are looking for.

within_text:  [required] The text includes the text we are looking for.

start_num:  [optional] Defines the position where the search should begin. Character 1 is the first character within the text. If the start num is not specified, it is considered to be 1.

Return Value:

The find text location is represented by a number.


Introduction to the SEARCH Function in Excel

The SEARCH function allows searching for objects in the database using simple or complex search. This function gives the position of one text string inside another. It is categorized under Excel String/Text Function.

Syntax:

The syntax for the SEARCH function is:

SEARCH(find_text,within_text,[start_num])

Arguments:

find_text:  [required] The text that searches.

within_text:  [required] The text to search inside.

start_num:  [optional] Position to begin searching in the text. The default value is 1.

Return Value:

The position of the find text is represented by a number.


Excel FIND Function VS Excel SEARCH Function

In Excel, the FIND function and SEARCH function are quite similar and do the same goal, but in slightly different yet crucial ways. The primary distinction between Excel FIND and SEARCH functions is that SEARCH is case-insensitive, whereas FIND is case-sensitive. Another difference is that SEARCH allows for the use of wildcards whereas FIND does not.


5 Examples with the Comparative Outputs from the FIND and SEARCH Functions in Excel

The FIND and SEARCH capabilities in Excel are rarely utilized independently. They are typically used in conjunction with other functions such as MIDLENLEFT, or RIGHT, and IFERROR we will see those in the examples below.

1. Insert Excel FIND & SEARCH Function in a Text Cell

We can insert FIND and SEARCH functions to detect the position of that specific word or letter. Suppose, we want to find ‘e’ in Excel, so we put the text string in column B, and the result of the position for the text string is in column C. Follow the below steps:

  • So, firstly, we are going to use the FIND function to find the position of ‘e’. For this, we need to select cell C5. After that, put the formula into that cell.
=FIND("e",B5)
  • Then, pressing Enter on the keyboard will show the result which is 4. The formula returns 4 because e is the 4th letter in the word Excel.
  • Now, secondly, we are going to use the SEARCH function to search the position of ‘e’. To do this, we have to choose the cell where we want to see the result, so we choose cell C6. Now, in that particular cell, enter the formula.
=SEARCH("e",B6)
  • And, press Enter. And, we can see that the result is 1. The formula returns 1 because E is the first character in word Excel. As we know the SEARCH function is not case sensitive like the FIND function, so it doesn’t care if the letter is capital or small. When it finds the letter it will immediately show the result.

Find and Search Function in Excel

  • So, by using the FIND and SEARCH functions we can discover the position of any word or letter in a text string.

Read More: How to Find Text in Cell in Excel


2. Apply FIND Function to Find a String That Comes before or after a Given Character

Suppose, we have some names in column B and we want to find the first and last name of each name respectively in columns C, and D. To do this, we need to follow the below procedure:

  • First, to get the first name, we select cell C5. We can use the FIND or SEARCH function in combination with the LEFT function. So, we are using the FIND function.
  • Second, put the formula in cell C5.
=LEFT(B5, FIND(" ", B5)-1)
  • Then, press Enter.

Find and Search Function in Excel

  • Now, we want to get the last name, for this, we can use the conjunction of the RIGHTFIND or SEARCH and LEN functions. So, by the same token as before, select cell D5 and put the formula in there.
=RIGHT(B5,LEN(B5)-FIND(" ",B5))
  • After pressing the Enter key, you will get the last name in the resulting cell.

Here, the LEN function will accrue the total number of characters, and then the FIND function will subtract the position of the space. And finally, the RIGHT function will show the right-sided characters.

  • Next, to copy the formula over column C, drag the Fill Handle down or just double click on the plus (‘+’) sign.

Find and Search Function in Excel

  • Similarly, drag the Fill Handle over column D to duplicate the formula.

Find and Search Function in Excel

  • Finally, this will extract all the first names and last names from the names.

Note: We use the FIND function instead of SEARCH because we want to get a more accurate value, As we all know that the FIND function is case sensitive.

Read More: How to Find a Character in String in Excel


3. Insert FIND Function to Find the Nth occurrence of a Particular Character in Excel

Assume that, we have some text string with a special character dash (‘’) in column B. And we want to find the 2nd and 3rd positions of the dash (‘’) consecutively in columns C, and D. To get the position of the special character, let’s follow the steps down.

  • Likewise the earlier examples, first, select the cell in your spreadsheet where you want to see the result after using the formula. So, we select cell C5.
  • Second, put the formula into that cell.
=FIND("-", B5, FIND("-",B5)+1)
  • Third, press Enter to see the result.

Here, the formula is to get the 2nd  position.

  • Further, drag the Fill Handle down to copy the formula.

  • Next, we want to get the 3rd position in column D. So, we select cell D5 and put the formula there.
=FIND("-",B5, FIND("-", B5, FIND("-",B5)+1) +2)
  • Furthermore, press the Enter key on the keyboard.

Find and Search Function in Excel

Here, the formula will show the 3rd position of the special character.

  • And, now, again drag the Fill Handle down to duplicate the formula over column D.
  • Finally, by following the steps, you will get your desired result.

Read More: How to Find Character in String Excel (8 Easy Ways)


Similar Readings


4. Apply SEARCH Function to Find Text between Parentheses

Suppose, we have some text string, and we want to get only the text which is enclosed by brackets. To get those text strings in the Parenthesis we need the MID function to separate the necessary number of characters from a string. As well as the FIND or SEARCH functions to figure out what to begin and how many letters to separate. Let’s follow the steps below.

  • In the beginning, select the cell, where you want to see the result. So, we select cell C5.
  • Then, copy and paste the formula into that cell.
=MID(B5,SEARCH("(",B5)+1, SEARCH(")",B5)-SEARCH("(",B5)-1)
  • Next, press Enter.

Find and Search Function in Excel

All you need to do is change the cell number as per your cell location, after pasting the formula.

  • After that, drag the Fill Handle down to duplicate the formula.

  • And, finally, the text inside the parenthesis is now extracted from the whole text string.

🔎 How Does the Formula Work?

SEARCH(“(“, B5)+1: This will take the cell value from cell B5, and search for the position of the opening bracket ‘(’ which is 4+1, from SEARCH(“(“, B5) we will get 4, cause the space is also counted.

Output → 5, which is the first letter inside the parenthesis ‘0’.

SEARCH(“)”, B5)-SEARCH(“(“, B5)-1: This will find the position of the closing bracket ‘)’. And, subtract the opening bracket position.

Output → 10-4-1; 6-1; 5, which is the last letter inside the parenthesis ‘1’.

MID(B5,SEARCH(“(“,B5)+1, SEARCH(“)”,B5)-SEARCH(“(“,B5)-1): This will extract the text inside the bracket.

Output → 01001

Read More: Excel Search for Text in Range (11 Quick Methods)


5. Combine FIND & IFERROR Functions to Handle the Error to Find Text

If the find_text is not found then it shows an error message in both FIND and SEARCH functions. Rather than showing the error message we may use the IFERROR function for displaying an expressive message like ‘Not Found’. For example, in cell C10, a user can input any text and cell C5 will be searched for the entered text.

  • First, select the cell where you want the position of the imputed text in cell C10. So, we select cell C11.
  • Second, put the formula in cell C11b.
=IFERROR(FIND(C10, C5), "Given text is not found!")
  • Then, press the Enter key on your keyboard. As you can see, it will show 1. Because the text Excel is found only one time in cell C5.

Find and Search Function in Excel

  • The text Indesign is not found in cell C5. So, it will give a message ‘Given text is not found!’.

Read More: How to Use Formula to Find Bold Text in Excel


Things to Keep in Mind

If any of the following conditions are met, the Excel FIND and SEARCH function produces the #VALUE! error:

  • Within text does not have the function find_text.
  • within_text has fewer characters than start_num.
  • start_num is either zero (‘0’) or a negative value or start_num is less than or equal to zero (‘0’).

Conclusion

The above will give you an overview of the FIND and SEARCH functions in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo