Excel Find

Find in Excel (Table of Contents)

• Using Find and Select Feature in Excel
• FIND Function in Excel
• SEARCH Function in Excel

Introduction to Find in Excel

There are two ways to Find in Excel. First, we can use Find by pressing Ctrl + F shortcut keys. Therein Find and Replace box, search the word or field which we want to find in Find What section. In another way, we can use the FIND function. For this, select the Find function from insert function and as per syntax select the substring from where we need to find, and choose the word or letter or number which we want to find in String position. This will return the position of 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.

Here, we have a record of marks of four students. Suppose we want to find text ‘envy’ in this table. For this, we click Find and Select under the Home tab then Find and Replace dialog box appears. In the find what box, we enter ‘envy’ then click on Find All. We get the text ‘envy’ is in the cell number A5.

• 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’. So, 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 the 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 which starts with letter ‘k’ and ends with letter ‘n’ with a single character between them. So, in the Find and Replace dialog box, we enter ‘k?n’ in the find what box. Then click on Find All.

Here, we get the text ‘k?n’(kin) is in the 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 further define your search if needed.
• We can find text or number by changing settings in the Within, Search and Look in box according to our need.
• To show the working of above-mentioned options, we took data as follows.

• 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. Example, we have a table of some cars’ names. Type ‘Creta’  in the Find What box.

• Then it will find cells which contain exactly ‘Creta’ and cells that contain ‘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 and 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 non-compulsory parameter and describes from which position we want search substring. If you not describe it then start search from the 1^st position.

For example =FIND(“o”, “Cow”) gives 2 because “o” is the 2^nd  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.

For e.g., the formula FIND(“ur”,”hurry”) gives 2 because “u” in the 2^nd 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:

1.  If Find_Value does not exist in Within_String.
2.  If Start_Position contains multiple characters as compared to Within_String.
3.  If Start_Position either has 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 blank string “”, the Excel FIND formula gives the first character in the string.

Example =SEARCH (“ful“, “Beautiful) gives 7 because the substring “ful” begins at the 7^th position of the substring “beautiful”.

=SEARCH (“e”, “MSExcel”) gives 3 because “e” is the 3rd character in the word “MSExcel” and ignoring the case.

• Excel’s SEARCH function gives the #VALUE! error if:

1. If the value of the Find_Value parameter is not found.
2. If the Start_Position parameter is superior than the length of Within_String.
3. If the Start_Position 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 arrow next to Format, click Choose Format From Cell, and click cell that has the formatting you want to search for.
• MSExcel saves the formatting options that you define, you should clear the formatting options from last search by click on an arrow next to Format and then Clear Find Format.
• The FIND function is case sensitive and does not allow while using 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 Find feature, Formula for FIND and SEARCH in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. How to use FIND Function in Excel?
2. Guide to SEARCH Function in Excel
3. Guide to Row Count in Excel
4. How to use Excel REPLACE Function?