What is FIND Function in Excel?
Find function is also one of the most under rated function in excel, which is used to find the text string stored in any cell and returns the position of that cell as output. The most important thing about the Find function is, the cell, which we have taken as a reference, should be in text format. Otherwise, we will end up getting a message as a #Value error message. This also happens; when the Find function is unable to find any text in the selected cell, it eventually returns the #Value error here again.
FIND Formula in Excel
The FIND formula is used to return the position of a substring or special character within a text string.
The Formula for the FIND function is as follows:
The first two arguments are mandatory; the last one is optional.
- find_text (required)- the substring or character you are looking to find.
- within_text (required)- the text string that is to be searched within. Generally, it’s supplied as a cell reference, but you can also type the string directly in the formula.
- start_num (optional)- an option that specifies the position in the within_text string or character from which the search should begin.
If excluded, the search starts from the default value 1 of the within_text string.
If the requested find_text is found, the Find function returns a number that represents the position of the within_text. If the supplied find_text is not found, the function returns the Excel #VALUE! error.
How to Use the FIND Function in Excel?
This FIND Function is very simple easy to use. Let us now see how to use the Find function with the help of some examples.
Example #1
In column C of the below spreadsheet, the Find function is used to find various characters in the text string “Original Text”.
The formula used to find the original Text using the FIND function is given below :
Since the Find function is case-sensitive, the lower- and upper-case values, “T” and “t”, gives results which are different (example in cells D4 & D5).
In cell D7, the argument[start_num] is set to 4. Hence the search begins from the fourth character of the within_text string, and so the function gives the second instance of the substring “i”.
Example #2
Suppose we wish to find some of the characters from the below data:
16 Online Courses | 23 Hands-on Projects| 140+ Hours| Verifiable Certificate of Completion
4.8
View Course
- Data is incorrect
- Revenue increased by 5%
- Original Text
Note that in the above spreadsheet:
- Due to the case-sensitive feature of the FIND function, the uppercase find_text value, “T”, will return 3 as its position.
- Since [start_num] argument is set to 1, the search begins at the first character of the within_text string in cell B8.
Example #3
Find value in a range, worksheet or workbook
The below guidelines will let you know how to find text, specific characters, numbers or dates in a range of cells, worksheet or the entire workbook.
- To start with, select the range of cells to check-in. To search across the whole worksheet, click on any of the cells on the active sheet.
- Open the Find and Replace dialog by pressing the shortcut key Ctrl + F. Alternatively, toggle to the Home tab > Editing group and click Find & Select > Find…
In the Find what box under Find, enter the characters (number or text) you are looking for and click on either Find All or Find Next.
When clicked on Find All, a list of all the occurrences gets opened in Excel, and you can toggle at any of the items in the list to navigate to the nearby cell.
When clicked on Find Next, the first occurrence of the search value on the sheet is selected in Excel, the second click selects the second occurrence, and so on.
FIND Function – additional options:
To fine-tune the search, click on the Options in the right-hand corner of Excel’s Find & Replace dialog, and then try any of the below options:
- To search from the active cell from left to right (row-wise), select By Rows in the Search To check from top to bottom (columnwise), select By Columns.
- To search for a specific value in the entire workbook or current worksheet, select Workbook or Sheet in the Within.
- To search for cells that have only the characters you have entered in the Find what field, select the Match entire cell contents.
- To search among some of the data type, Formulas, Values, or Comments in the Look in.
- For a case-sensitive search, check the Match case check.
Note: If you want to find a given value in a range, column or row, select that range, column(s) or row(s) before opening Find and Replace in Excel. For example, to limit your search to a specific column, select that column first, and then open the Find and Replace dialog.
Find cells with a specific format in Excel:
For finding cells with certain or specific formatting, press the shortcut keys Ctrl + F to open the Find and Replace dialog, click Options, then click the Format… button in the upper right corner, and define your selections in the Find Format dialog box.
To find cells that match a format of some other cell on your worksheet, delete any criteria in the Find what box, click the arrow next to Format, select Choose Format From Cell, and click the cell with the desired formatting.
Note: Microsoft Excel saves the specified formatting. If you search for any other data on a worksheet and Excel is unable to find those values that you know are present, then try clearing the formatting options from the previous search. For doing this, open the Find and Replace dialog, click or select the Options button on the Find tab, then click the arrow beside Format.. and select Clear Find Format.
FIND Function Error:
If you are getting an error from the Find function, this is likely to be the #VALUE! error:
Things to Remember
To correctly use the FIND formula, always remember the following simple facts:
- The FIND function is case sensitive. If you are looking for a match which is case-insensitive, use Excel’s SEARCH function.
- The FIND function does not permit to use of wildcard characters.
- If the find_text argument contains duplicate characters, the FIND function returns the first character’s position. For example, the formula FIND(“xc”, “Excel”) returns 2 because “x” is the 2nd letter in the word “Excel”.
- If within_text has several occurrences of find_text, the first occurrence is returned. For example, FIND(“p”, “Apple”) returns 2, which is the position of the first “p” character in the word “Apple”.
- If find_text is the string is empty “, the FIND formula gives back the first character in the search string.
- The FIND function returns the #VALUE! error if any of the following occurs:
- Find_text does not exist in within_text.
- Start_num contains more characters than within_text.
- Start_num is 0 (zero) or a negative number.
Recommended Articles
This is a guide to the FIND function in Excel. Here we discuss the FIND Formula and how to use FIND Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
