FIND and SEARCH in Excel - Step-by-Step Guide

Introduction

FIND and SEARCH help you find where a word or letter appears inside a text. They tell you the position number of the first letter found. This is useful when you want to locate parts of text in a cell.

When you want to find the position of a specific word in a sentence in a cell.

When you need to check if a letter or word exists inside a text and where it starts.

When you want to extract part of a text based on where a word appears.

When you want to find text without worrying about uppercase or lowercase letters.

When you want to find text but need the search to be case sensitive.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for formula input

Step 2: Type

- the formula bar

Formula bar shows the formula you type

💡 Start typing =FIND or =SEARCH to see formula suggestions

Step 3: Type

- the formula bar

Formula bar shows the full formula with arguments

💡 Example: =FIND("a", A1) finds letter 'a' in cell A1

Step 4: Press

- Enter key

The cell shows the position number of the first letter found

Step 5: Repeat

- for SEARCH formula if case-insensitive search is needed

SEARCH finds text ignoring uppercase or lowercase differences

Before vs After

Before

Cell A1 contains the text 'Hello World'

After

Using =FIND("o", A1) returns 5 because 'o' is the 5th letter in 'Hello World'

Settings Reference

Find Text

📍 First argument in FIND or SEARCH formula

The text you want to find inside another text

Default: No default, must specify

Within Text

📍 Second argument in FIND or SEARCH formula

The text where you want to search for the find text

Default: No default, must specify

Start Number

📍 Optional third argument in FIND or SEARCH formula

Position to start searching from inside the text

Default: 1

Common Mistakes

Using FIND when you want to ignore case differences

FIND is case sensitive and will not find letters if case does not match

Use SEARCH instead, which ignores case differences

Not putting the text to find inside quotes

Excel treats unquoted text as a cell reference or name, causing errors

Always put text strings inside double quotes, like "a"

Searching for text that does not exist in the cell

FIND and SEARCH return an error if text is not found

Use IFERROR to handle errors, e.g. =IFERROR(FIND("x", A1), "Not found")

Summary

FIND and SEARCH locate the position of text inside another text.

FIND is case sensitive; SEARCH is case insensitive.

Both return the position number or an error if text is not found.

Practice

1. What is the main difference between the FIND and SEARCH functions in Excel?

easy

A. FIND returns the length of the text; SEARCH returns the position.

B. SEARCH only works with numbers; FIND works with text.

C. FIND is case-sensitive; SEARCH is not case-sensitive.

D. SEARCH is case-sensitive; FIND is not case-sensitive.

Solution

1. Step 1: Understand the behavior of FIND

   The FIND function looks for a substring inside text and is case-sensitive, meaning it treats uppercase and lowercase letters differently.

2. Step 2: Understand the behavior of SEARCH

   The SEARCH function also finds a substring but ignores case differences, so it is not case-sensitive.

3. Final Answer:

   FIND is case-sensitive; SEARCH is not case-sensitive. -> Option C

4. Quick Check:

   Case sensitivity difference = FIND is case-sensitive; SEARCH is not case-sensitive. [OK]

Hint: Remember: FIND cares about case, SEARCH ignores it [OK]

Common Mistakes:

• Thinking SEARCH only works with numbers
• Confusing which function is case-sensitive
• Believing FIND returns length instead of position

2. Which of the following is the correct syntax for the FIND function in Excel?

easy

A. =FIND(find_text, within_text, [start_num])

B. =FIND(within_text, find_text, [start_num])

C. =FIND(find_text, start_num, within_text)

D. =FIND(start_num, find_text, within_text)

Solution

1. Step 1: Recall the order of arguments in FIND

   The correct order is find_text (the text to find), within_text (where to look), and optional start_num (where to start).

2. Step 2: Match the syntax with options

   =FIND(find_text, within_text, [start_num]) matches this order exactly, so it is correct.

3. Final Answer:

   =FIND(find_text, within_text, [start_num]) -> Option A

4. Quick Check:

   Correct argument order = =FIND(find_text, within_text, [start_num]) [OK]

Hint: Remember: FIND(find_text, within_text, start_num) [OK]

Common Mistakes:

• Swapping find_text and within_text arguments
• Placing start_num before within_text
• Using incorrect argument order causing errors

3. Given cell A1 contains the text "Hello World", what will the formula =FIND("o", A1) return?

medium

A. 8

B. 5

C. 7

D. 6

Solution

1. Step 1: Identify the position of "o" in "Hello World"

   The text is "Hello World". Counting letters: H(1) e(2) l(3) l(4) o(5) space(6) W(7) o(8) r(9) l(10) d(11). The first "o" appears at position 5.

2. Step 2: Understand FIND returns first occurrence position

   FIND("o", A1) returns the position of the first "o", which is 5.

3. Final Answer:

   5 -> Option B

4. Quick Check:

   First "o" position = 5 [OK]

Hint: Count characters to find first match position [OK]

Common Mistakes:

• Choosing position of second 'o' instead of first
• Counting space incorrectly
• Confusing FIND with SEARCH case sensitivity

4. You have the formula =FIND("apple", B1) but it returns an error. Which fix will most likely solve the problem?

medium

A. Replace FIND with SEARCH to ignore case

B. Add a start number argument like =FIND("apple", B1, 5)

C. Use =FIND(B1, "apple") instead

D. Change "apple" to "Apple" if B1 contains "Cherry pie"

Solution

1. Step 1: Understand FIND is case-sensitive

   If B1 contains "Apple pie" with capital A, =FIND("apple", B1) will error because "apple" (lowercase) is not found exactly.

2. Step 2: Use SEARCH to ignore case

   Replacing FIND with SEARCH ignores case differences, so =SEARCH("apple", B1) will find "Apple" regardless of case.

3. Final Answer:

   Replace FIND with SEARCH to ignore case -> Option A

4. Quick Check:

   Use SEARCH for case-insensitive find [OK]

Hint: Use SEARCH to avoid case errors [OK]

Common Mistakes:

• Swapping arguments incorrectly
• Adding start_num without fixing case
• Changing text case without changing function

5. You want to find the position of the second occurrence of the letter "e" in cell A2 containing "Experience". Which formula will correctly return this position?

hard

A. =SEARCH("e", A2, 2)

B. =SEARCH("e", A2) + 1

C. =FIND("e", A2, 2)

D. =FIND("e", A2, FIND("e", A2) + 1)

Solution

1. Step 1: Find first occurrence position of "e"

   Using FIND("e", A2) returns the position of the first "e" in "Experience".

2. Step 2: Use that position + 1 as start_num to find second occurrence

   To find the second "e", start searching just after the first found position: FIND("e", A2, FIND("e", A2) + 1).

3. Final Answer:

   =FIND("e", A2, FIND("e", A2) + 1) -> Option D

4. Quick Check:

   Nested FIND finds second occurrence [OK]

Hint: Nest FIND to locate second occurrence [OK]

Common Mistakes:

• Using fixed start_num without first FIND
• Adding 1 to SEARCH result incorrectly
• Using SEARCH with wrong start_num