EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources TEXT and String Functions in Excel Excel Find
Secondary Sidebar
Excel Functions
  • TEXT and String Functions in Excel
    • Excel CODE Function
    • Excel Find
    • TRIM Formula in Excel
    • Excel CHAR Function
    • LEFT Formula in Excel
    • Opposite of Concatenate in Excel
    • Concatenate Strings in Excel
    • Extract Numbers From String
    • VALUE Formula in Excel
    • Left TRIM in Excel
    • Substring Excel Function
    • SUBSTITUTE Function in Excel
    • Excel VALUE Function
    • Excel REPT Function
    • Excel TRIM Function
    • Excel CLEAN Function
    • Excel EXACT Function
    • PROPER Function in Excel
    • Excel LEFT Function
    • MID Function in Excel
    • LEN Function in Excel
    • CONCATENATE Function in Excel
    • SEARCH Function in Excel
    • RIGHT Function in Excel
    • FIND Function in Excel
    • TEXT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online COURSE
  • Online Excel Data Analysis Training

Excel Find

By Madhuri ThakurMadhuri Thakur

Find in excel

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 it 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 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.

You can download this Find in Excel Template here – Find in Excel Template

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Step 1 – Under the Home tab, in the Editing group, click Find & Select.

find in excel method 1-1

Step 2 – To find text or numbers, click Find.

find in excel method 1-2

  • 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.

find in excel method 1-3

Here, we have a record of marks of four students. 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’ then click on Find All. We get the text ‘envy’ is in cell number A5.

find in excel method 1-4

  • 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.

find in excel method 1-5

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.

find in excel method 1-6

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.

Find and Replace

Here, we get 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.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)

find in excel method 1-8 

  • 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 the box according to our needs.
  • To show the working of the above-mentioned options, we took the data as follows.

find in excel method 1-9

  • 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’.

find in excel method 1-10

  • 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.

Match entire cell contents 1-11

  • It will then find cells containing exactly ‘Creta’, and cells containing ‘Cretaa’ or ‘Creta car’ will not be found.

find in excel method 1-12

  • 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.

Click the Font option 1-13

  • Then, we click on Find All.

 find in excel method 1-14

We get the value as ‘elisa’, which is in the ‘A3’ cell.

find in excel method 1-15

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:

Find Formula

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 substring. If you do not describe it, then 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 in excel method 2-1

FIND(“j”, “Cow”) gives an error because there is no “j” in “Cow”.

Value Error 2-2

  • 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” in the 2nd letter in the word “hurry”.

find in excel method 2-3

  • If Within_String contains multiple occurrences of Find_Value, the first occurrence is returned. For example, FIND (“o”, “wood”)

find in excel method 2-4

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 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.

SEARCH Formula

  • If Find_Value is the blank string “, the Excel FIND formula gives the string’s first character.

SEARCH Function method 3-1

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

SEARCH Function method 3-2

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

SEARCH Function method 3-3

  • 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 to 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 the arrow next to Format, click 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 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 the Find feature, Formula for FIND, and SEARCH in Excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. FIND Function in Excel
  2. Excel SEARCH Function
  3. Find and Replace in Excel
  4. Search For Text in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Find in Excel Template

EDUCBA

Download Find in Excel Template

EDUCBA

डाउनलोड Find in Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more