FIND Function in Excel Tutorial

Excel Tutorial

The FIND function in Excel helps you locate the position of a specific character or text within another text value. It is useful when working with employee IDs, product codes, email addresses, invoice numbers, and other business data.

FIND returns the character position where the search text begins. It is case-sensitive, which means uppercase and lowercase letters are treated differently.

FIND Function Syntax

=FIND(find_text, within_text, [start_num])

Arguments:

  • find_text: The character or text you want to find.
  • within_text: The text or cell where you want to search.
  • start_num: Optional. The position from which Excel should begin searching. If omitted, Excel starts from position 1.

Important Points:

  • FIND is case-sensitive.
  • FIND counts spaces and special characters as positions.
  • FIND returns the position of the first matching occurrence from the specified starting position.
  • If the search text is not found, Excel returns a #VALUE! error.

Question 1: Find the Position of a Character in an Employee Name

Problem Statement

An HR department wants to find the position of the letter i in employee names.

Employee NameSearch CharacterPosition
Rishabhi3
Priyai3
Amiti3
Nitini2

Assume employee names are in A2 and the search character is in B2.

Excel Formula

=FIND(B2,A2)

Expected Result: For Rishabh, the result is 3 because the letter i appears at the third position.

Explanation: FIND counts each character from left to right and returns the position of the first matching character.

Student Task: Enter Rahul in A6 and h in B6. Find the position.

New Expected Result: 3

Question 2: Find the Position of a Hyphen in Employee IDs

Problem Statement

A company uses hyphens in employee IDs to separate different parts of the ID. The HR department wants to find the position of the first hyphen.

Employee IDPosition of Hyphen
EMP-HR-10454
EMP-IT-20864
STAFF-FN-30126
VSIT-EMP-1055

Assume employee IDs are in A2.

Excel Formula

=FIND("-",A2)

Expected Result: For EMP-HR-1045, the result is 4.

Explanation: The first hyphen appears after the three letters EMP, so its position is 4.

Student Task: Enter TEAM-IT-205 in A6 and find the position of the hyphen.

New Expected Result: 5

Question 3: Find the Position of a Word in a Course Name

Problem Statement

A training institute wants to find where the word Excel begins in different course names.

Course NameSearch TextPosition
Advanced Excel CourseExcel10
Basic Excel TrainingExcel7
Learn Excel OnlineExcel7
Complete Excel GuideExcel10

Assume course names are in A2 and the search text is in B2.

Excel Formula

=FIND(B2,A2)

Expected Result: For Advanced Excel Course, the result is 10.

Explanation: The word Excel starts at the tenth character in Advanced Excel Course, including the space after Advanced.

Student Task: Enter Professional Excel Training in A6 and Excel in B6. Find the starting position.

New Expected Result: 15

Question 4: Find the Position of @ in an Email Address

Problem Statement

A company wants to locate the @ symbol in employee email addresses.

Email AddressPosition of @
amit@gmail.com5
priya@yahoo.com6
rahul@company.com6
neha@vsit.in5

Assume email addresses are in A2.

Excel Formula

=FIND("@",A2)

Expected Result: For amit@gmail.com, the result is 5.

Explanation: The @ symbol appears after the four characters amit, so its position is 5.

Student Task: Enter rohit@company.in in A6 and find the position of @.

New Expected Result: 6

Question 5: Find a Character Starting from a Specific Position

Problem Statement

A company uses product codes containing repeated characters. The inventory department wants to find the position of the second hyphen in each code.

Product CodeSearch CharacterStarting PositionExpected Position
LAP-IT-105–57
KEY-HW-205–57
MON-DP-310–57
USB-AC-450–57

Assume product codes are in A2.

Excel Formula

=FIND("-",A2,5)

Expected Result: For LAP-IT-105, the result is 7.

Explanation: The formula starts searching from position 5, so it finds the second hyphen instead of the first one.

Student Task: Enter MOUSE-WL-250 in A6. Find the second hyphen by starting the search from position 7.

New Expected Result: 8

Student Tasks

Solve the following exercises using only the FIND function.

Task 1: Find the Position of a Character

TextSearch CharacterPosition
COMPUTERP?
KEYBOARDB?
MONITORT?
PRINTERE?

Your Task: Find the position of each specified character.

Expected Results: 4, 5, 5, 5

Task 2: Find the Position of a Special Character

CodeSearch CharacterPosition
EMP-HR-101–?
INV-2026-50–?
IT-TEAM-205–?
SALES-EMP-10–?

Your Task: Find the position of the first hyphen in each code.

Expected Results: 4, 4, 3, 6

Task 3: Find Text Starting from a Specific Position

Product CodeSearch CharacterStarting PositionPosition
LAP-IT-105–5?
KEY-HW-205–5?
MON-DP-310–5?
USB-AC-450–5?

Your Task: Find the second hyphen in each product code.

Expected Results: 7, 7, 7, 7

Final Student Challenge: Build an Employee Code Position Checker

Problem Statement

An HR department wants to check the position of the hyphen in employee IDs and the position of the @ symbol in employee email addresses.

Given Data

Employee NameEmployee IDEmail Address
Amit KumarEMP-HR-101amit@company.com
Priya SharmaEMP-IT-202priya@company.com
Rahul VermaEMP-FN-303rahul@company.com
Neha GuptaEMP-MK-404neha@company.com
Rohit SinghEMP-OP-505rohit@company.com

Your Task

Add two columns:

  • Hyphen Position
  • @ Position

Use the FIND function to locate the first hyphen in each employee ID and the @ symbol in each email address.

Formula for Hyphen Position:

=FIND("-",B2)

Formula for @ Position:

=FIND("@",C2)

Copy both formulas down for all employees.

Expected Result

Employee NameEmployee IDHyphen PositionEmail Address@ Position
Amit KumarEMP-HR-1014amit@company.com5
Priya SharmaEMP-IT-2024priya@company.com6
Rahul VermaEMP-FN-3034rahul@company.com6
Neha GuptaEMP-MK-4044neha@company.com5
Rohit SinghEMP-OP-5054rohit@company.com6

Key Takeaways

  • FIND locates the position of a character or text within another text value.
  • Its syntax is =FIND(find_text, within_text, [start_num]).
  • FIND is case-sensitive.
  • It counts spaces and special characters.
  • The optional start_num argument lets you begin searching from a specific position.
  • FIND returns the position of the first matching occurrence from the starting position.
  • If the search text is not found, Excel returns #VALUE!.

FAQs

1. What is the FIND function in Excel?

The FIND function locates a specific character or text within another text value and returns its starting position.

2. What is the syntax of the FIND function?

The syntax is =FIND(find_text, within_text, [start_num]).

3. Is the FIND function case-sensitive?

Yes. FIND distinguishes between uppercase and lowercase letters. For example, searching for A is different from searching for a.

4. What happens if FIND cannot locate the text?

Excel returns a #VALUE! error when the specified character or text is not found.

5. What is the use of start_num in FIND?

The optional start_num argument tells Excel where to begin searching. It is useful when the same character appears multiple times.

6. Does FIND count spaces?

Yes. Spaces are counted as characters when Excel calculates the position.

7. Can FIND search for a complete word?

Yes. You can search for a complete word or a group of characters, and FIND returns the starting position of the matching text.

8. Can FIND locate special characters?

Yes. FIND can locate symbols such as @, -, /, and # within text.