EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources TEXT and String Functions in Excel Excel Find

Excel Find

Madhuri Thakur
Article byMadhuri Thakur

Updated August 16, 2023

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

Start Your Free Excel Course

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

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.

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

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.

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

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.

find in excel method 1-8 

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

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, 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 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 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” is 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 (“full“, “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 ignores 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 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 –

  1. FIND Function in Excel
  2. Excel SEARCH Function
  3. Find and Replace in Excel
  4. Search For Text in Excel
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

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

EDUCBA

Download Find in Excel Template

EDUCBA

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

Let’s Get Started

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

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

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

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW