FIND Function in Excel: Explained
In this article, you will learn about the Find Function and its uses in Excel.
What is a FIND function in Excel?
In Microsoft Excel, the FIND function is used to locate and return the position of a specific character or substring within a text string. It helps you identify the starting position of a particular text within a larger text string. The FIND function is case-sensitive, considering uppercase different from lowercase characters. If you need a case-insensitive search, you can use the SEARCH function instead, which works similarly to FIND but ignores the case.
Syntax of FIND formula in Excel
The syntax of the Excel FIND function is as follows:
find_text: This is the text you want to find within the larger text string.
within_text: This is the text string in which you search for the find_text.
start_num: (optional) This argument specifies the character at which you want to start the search. If omitted, the search begins from the first character.
The FIND function returns the position of the first character of the defined “find_text” within the within_text. If the find_text is not found, the function returns a #VALUE! Error.
How to use the FIND function in Excel?
Understand the FIND function by looking at a particular use case. The below dataset includes employee names and their employee identification numbers. Now the objective is to get the specific records from the database as per requirement.
Remember the syntax of the FIND function as follows:
find_text: Suppose we need to fetch all the employee names with the Title “Dr.”; hence the find_text value is “Dr.”
within_text: The text where we need to search would be the employee names, so the within_text value is A2.
start_num (optional): Since we want our search to start from the first character, we shall omit this parameter.
Our complete function would look like this:
Now we need to apply this function in the corresponding cell C2 and press enter to generate the function result.
As you can see, the returned value is “1”, which denotes the position of the text and confirms that the value “Dr.” exists in the selected employee name.
Now, you can drag and drop the formula, quickly apply the function to the remaining cells, and view the result.
The function will return #VALUE! Error if the text is not found in the referenced cell.
If the function returns any integers, those cells checked include “Dr.” as the title in their names. Thus, in the sample dataset, we can determine that three employees have the title of “Dr.”
Consider another case where you need to fetch the Employee IDs that contain the code “CF” as a part of the ID. The FIND formula should be as follows:
You can copy and paste the formula to the following rows to get the results for all Employee Names.
Imagine another scenario in which we search for a specific character after a particular position. For instance, we need to know all Employee IDs having “0” after the 5th character. We can write the function as below.
Now we shall apply the function in cell C2 to obtain the results for other IDs.
As you can see, the Employee IDs on row numbers 4,5, and 6 only return with the position of “0” since the start_num parameter was set at 5, which means the function will start search from the 5th character.
When should you use the FIND Function in Excel?
The FIND function in Excel is handy in several situations, as described below.
- Extracting specific data: You can use the FIND function to locate a specific character or substring within a larger text string and then extract the relevant information based on its position. For example, if you have a column of email addresses and you want to extract the domain name from each address, you can use FIND to find the position of the "@" symbol and then use other functions like LEFT, RIGHT, or MID to extract the desired portion.
- Parsing text: When you have text data that needs to be parsed or split into different parts, the FIND function can be used as a starting point. For instance, if you have a column containing full names and you want to separate the first name and last name into separate columns, you can use FIND to find the position of the space character and then use LEFT and RIGHT functions to extract the respective parts.
- Replacing or removing characters: The FIND function can help you identify the position of specific characters or substrings that you want to replace or remove from a text string. Once you have located the position using FIND, you can combine it with other functions like REPLACE or SUBSTITUTE to make the desired modifications.
- Conditional formatting: If you want to highlight or format cells based on the presence or position of a specific text, the FIND function can be used within conditional formatting rules. You can set up rules that trigger formatting when a certain character or substring is found within a cell, allowing you to draw attention to specific data patterns or anomalies.
What is the key difference between FIND and SEARCH formulas in Excel?
It's essential to know that the FIND function is case-sensitive, distinguishing between uppercase and lowercase characters. This means that if you're searching for "apple" using the FIND function, it will only find the lowercase "apple" and not the uppercase "APPLE" or mixed-case variations. If you need to perform a case-insensitive search where the function ignores the case of the text, you can use the SEARCH function instead. The SEARCH function works similarly to FIND but doesn't differentiate between uppercase and lowercase characters. You can use it in the same way as the FIND function, but with the knowledge that it will treat all characters as case-insensitive. So, depending on your specific needs, you can choose between the FIND function (case-sensitive) or the SEARCH function (case-insensitive) to locate and work with text in Excel.
