Find in Excel (Table of Contents)
Introduction to Find in Excel
There are two ways to find it in Excel. First, we can use Find by pressing Ctrl + F shortcut keys. In the Find and Replace box, search the word or field we want to find in the Find What section. In another way, we can use the FIND function. For this, select the Find function from the insert function and, as per syntax, select the substring from where we need to find it, and choose the word or letter or number which we want to find in the String position. This will return the position of the chosen string from the selected Substring.
Methods to Find in Excel
Below are the different methods to find in Excel.
Method #1 – Using Find and Select Feature in Excel
Let’s see How to Find a Number or a Character in Excel using the Find and Select feature in Excel.
Step 1 – Under the Home tab, in the Editing group, click Find & Select.
Step 2 – To find text or numbers, click Find.
- In the Find What box, type the text or character you want to search for, or click the arrow in the Find What box and then click a recent search in the list.
Suppose we want to find the text ‘envy’ in this table. For this, we click Find and Select under the Home tab then the Find and Replace dialog box appears. In the Find What box, we enter envy and click on Find All. We get the text ‘envy’ in cell number A5. Here, we have a record of the marks of four students.
- You can use wildcard characters, such as an asterisk (*) or a question mark (?), in your search criteria:
Use the asterisk to find any string of characters.
Suppose we want to find text in the table which starts with the letter’ j’ and ends with the letter ‘n’. In the Find and Replace dialog box, we enter ‘j*n’ in the Find What box, then click on Find All.
We will get the result as text ‘j*n’ (john) is in cell no. ‘A2’ because we have only one text which starts with ‘j’ and ends with ‘n’ with any number of characters between them.
Use the question mark to find any single character.
Suppose we want to find text in the table that starts with the letter’ k’ and ends with the letter ‘n’ with a single character. So, in the Find and Replace dialog box, we enter ‘k? n’ to find what box. Then click on Find All.
The text ‘k?n’ (kin) is in cell no. ‘A4’ because we have only one text which starts with ‘k’ and ends with ‘n’ with a single character between them.
- Click Options to define your search further if needed.
- We can find text or numbers by changing settings in the Within, Search, and Look in the box according to our needs.
- We took the data as follows to show the working of the above-mentioned options.
- To search case-sensitive data, select the Match case check box. It gives you output in the case you give input in the Find What box. For example, we have a table of some cars’ names. If you type ‘ferrari’ in the Find What box, then it will find only ‘ferrari’, not ‘Ferrari’.
- To search for cells that contain just the characters you typed in the Find what box, select the Match entire cell contents checkbox. For example, we have a table of some cars’ names. Type ‘Creta’ in the Find What box.
- It will then find cells containing exactly ‘Creta’, and cells containing ‘Cretaa’ or ‘Creta car’ will not be found.
- If you want to search for text or numbers with specific formatting, click Format, and then make your selections in the Find Format dialog box according to your need.
- Let us click the Font option, select the Bold, and click OK.
- Then, we click on Find All.
We get the value as ‘elisa’, which is in the ‘A3’ cell.
Method #2 – Using FIND Function in Excel
The FIND function in Excel gives the location of a substring within a string.
Syntax For FIND in Excel:
The first two parameters are required, and the last parameter is non-compulsory.
- Find_Value: The substring which you want to find.
- Within_String: The string in which you want to find the specific substring.
- Start_Position: It is a non-compulsory parameter and describes from which position we want to search the substring. If you do not describe it, start the search from the 1st position.
For example =FIND(“o”, “Cow”) gives 2 because “o” is the 2nd letter in the word “cow“.
FIND(“j”, “Cow”) gives an error because there is no “j” in “Cow”.
- If the Find_Value parameter contains multiple characters, the FIND function gives the location of the first character.
E.g., the formula FIND(“ur”, “hurry”) gives 2 because “u” is the 2nd letter in the word “hurry”.
- If Within_String contains multiple occurrences of Find_Value, the first occurrence is returned. For example, FIND (“o”, “wood”)
gives 2, which is the location of the first “o” character in the string “wood”.
The Excel FIND function gives the #VALUE! error if:
- If Find_Value does not exist in Within_String.
- If Start_Position contains multiple characters as compared to Within_String.
- If Start_Position either has a zero or negative number.
Method #3 – Using SEARCH Function in Excel
The SEARCH function in Excel is simultaneous to FIND because it also gives the location of a substring in a string.
- If Find_Value is the blank string “, the Excel FIND formula gives the string’s first character.
Example =SEARCH (“full“, “Beautiful) gives 7 because the substring “ful” begins at the 7th position of the substring “beautiful”.
=SEARCH (“e”, “MSExcel”) gives 3 because “e” is the 3rd character in the word “MSExcel” and ignores the case.
- Excel’s SEARCH function gives the #VALUE! error if:
- If the value of the Find_Value parameter is not found.
- If the Start_Position parameter is superior to the length of Within_String.
- If the Start_Position is either equal to or less than 0.
Things to Remember About Find in Excel
- Asterisk defines a string of characters, and the question mark defines a single character. You can also find asterisks, question marks, and tilde characters (~) in worksheet data by preceding them with a tilde character inside the Find what option.
For example, to find data that contain “*”, you would type ~* as your search criteria.
- If you want to find cells that match a specific format, you can delete any criteria in the Find What box and select a specific cell format as an example. Click the arrow next to Format, Choose Format From Cell, and click the cell with the formatting you want to search for.
- MSExcel saves the formatting options you define; you should clear the formatting options from the last search by clicking an arrow next to Format and then Clear Find Format.
- The FIND function is case-sensitive and does not allow while use of wildcard characters.
- The SEARCH function is case-insensitive and allows while using wildcard characters.
Recommended Articles
This is a guide to Find in Excel. Here we discuss how to use the Find feature, Formula for FIND, and SEARCH in Excel, with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
