FIND and SEARCH in Excel - Deep Dive

Overview - FIND and SEARCH

What is it?

FIND and SEARCH are Excel functions used to locate the position of one text string inside another. They return the number of the character where the searched text starts. FIND is case-sensitive and does not allow wildcards, while SEARCH is case-insensitive and supports wildcards like ? and *. Both help you find text within cells quickly.

Why it matters

Without FIND and SEARCH, locating specific text inside larger text strings would be slow and manual. These functions let you automate tasks like extracting parts of text, validating data, or cleaning messy information. They save time and reduce errors in managing text data in spreadsheets.

Where it fits

Before learning FIND and SEARCH, you should understand basic Excel formulas and text data. After mastering these, you can learn related functions like LEFT, RIGHT, MID for extracting text, and SUBSTITUTE for replacing text. They fit into the broader topic of text manipulation in spreadsheets.

Mental Model

Core Idea

FIND and SEARCH tell you exactly where a smaller piece of text starts inside a bigger text string.

Think of it like...

It's like using a map to find the exact street number where a friend lives inside a long street address.

Text:  H e l l o   W o r l d
Index: 1 2 3 4 5 6 7 8 9 10
FIND("W", "Hello World") → 7

Build-Up - 7 Steps

1

Foundation

Basic use of FIND function

Concept: Learn how FIND locates text position with case sensitivity.

The FIND function syntax is FIND(find_text, within_text, [start_num]). It looks for find_text inside within_text starting at start_num (default 1). It returns the position of the first character of find_text. Example: FIND("a", "Banana") returns 2 because 'a' first appears at the second character.

Result

FIND("a", "Banana") returns 2.

Understanding FIND's case sensitivity helps you know it treats uppercase and lowercase letters differently, which affects search results.

2

Foundation

Basic use of SEARCH function

Concept: Learn how SEARCH locates text position ignoring case and supports wildcards.

SEARCH syntax is SEARCH(find_text, within_text, [start_num]). It works like FIND but ignores case and allows wildcards: '?' for any single character and '*' for any sequence. Example: SEARCH("a", "Banana") returns 2, same as FIND, but SEARCH("A", "Banana") also returns 2 because it ignores case.

Result

SEARCH("A", "Banana") returns 2.

Knowing SEARCH ignores case and supports wildcards makes it flexible for fuzzy or case-insensitive searches.

3

Intermediate

Using start_num to control search position

🤔Before reading on: If you start searching from character 4 in "Banana", will FIND("a", "Banana", 4) return 2 or 5? Commit to your answer.

Concept: Learn how the optional start_num argument changes where the search begins.

Both FIND and SEARCH let you specify start_num to begin searching from that character position. For example, FIND("a", "Banana", 4) starts looking from the 4th character. Since the first 'a' after position 4 is at position 5, it returns 5. This helps find multiple occurrences by moving the start point.

Result

FIND("a", "Banana", 4) returns 5.

Understanding start_num lets you find later occurrences of text, enabling more complex text parsing.

4

Intermediate

Difference between FIND and SEARCH functions

🤔Before reading on: Does FIND find 'A' in 'Banana' if searching for uppercase 'A'? Commit yes or no.

Concept: Compare how FIND and SEARCH handle case sensitivity and wildcards.

FIND is case-sensitive and does not support wildcards. SEARCH is case-insensitive and supports '?' and '*'. For example, FIND("A", "Banana") returns an error because uppercase 'A' is not found, but SEARCH("A", "Banana") returns 2. This difference guides which to use based on your needs.

Result

FIND("A", "Banana") returns #VALUE! error; SEARCH("A", "Banana") returns 2.

Knowing these differences prevents errors and helps pick the right function for your text search.

5

Intermediate

Using wildcards with SEARCH function

🤔Before reading on: Will SEARCH("b?n", "Banana") find a match? Commit yes or no.

Concept: Learn how to use '?' and '*' wildcards in SEARCH to match patterns.

The '?' wildcard matches any single character, '*' matches any number of characters. For example, SEARCH("b?n", "Banana") looks for 'b' followed by any character, then 'n'. It returns 1 because 'Ban' matches. This allows flexible pattern matching inside text.

Result

SEARCH("b?n", "Banana") returns 1.

Using wildcards expands SEARCH's power to find patterns, not just exact text.

6

Advanced

Handling errors when text not found

🤔Before reading on: What happens if FIND("x", "Banana") is used? Does it return 0, error, or something else? Commit your guess.

Concept: Learn how FIND and SEARCH behave when the text is missing and how to handle errors gracefully.

If FIND or SEARCH cannot find the text, they return a #VALUE! error. To avoid breaking formulas, wrap them in IFERROR or ISNUMBER checks. Example: IFERROR(FIND("x", "Banana"), 0) returns 0 instead of error. This is important for robust spreadsheets.

Result

FIND("x", "Banana") returns #VALUE! error; IFERROR(FIND("x", "Banana"), 0) returns 0.

Knowing error behavior and handling prevents formula crashes and improves user experience.

7

Expert

Combining FIND/SEARCH with other text functions

🤔Before reading on: Can you extract the domain from an email using FIND or SEARCH combined with MID? Commit yes or no.

Concept: Learn how to use FIND or SEARCH with MID, LEFT, RIGHT to extract parts of text dynamically.

You can find the position of a character (like '@') with FIND or SEARCH, then use MID to extract text after it. For example, to get domain from email in A1: MID(A1, FIND("@", A1) + 1, LEN(A1)). This extracts everything after '@'. Combining these functions allows powerful text parsing.

Result

If A1 contains "[e-mail]", formula returns "example.com".

Understanding how to combine these functions unlocks advanced text manipulation and data cleaning.

Under the Hood

FIND and SEARCH scan the text string character by character starting from start_num. FIND compares characters exactly including case, while SEARCH converts both strings to uppercase internally to ignore case and interprets wildcards. They return the index of the first matching character or an error if none found.

Why designed this way?

FIND was designed for exact, case-sensitive searches to match programming and data needs. SEARCH was added later to allow more flexible, user-friendly searches ignoring case and supporting wildcards. This separation keeps functions simple and specialized.

┌───────────────┐
│ Input Text    │
│ "Banana"    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ FIND/SEARCH   │
│ Search for   │
│ "a"         │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Compare chars │
│ (case-sensitive or
│  case-insensitive)
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Return index  │
│ or error      │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does FIND ignore case when searching text? Commit yes or no.

Common Belief: FIND ignores case and finds text regardless of uppercase or lowercase.

Reality: FIND is case-sensitive and only finds text that matches the exact case.

Why it matters: Using FIND expecting case-insensitivity causes missed matches and errors in data processing.

Quick: Can SEARCH find text using wildcards like '*'? Commit yes or no.

Common Belief: Neither FIND nor SEARCH support wildcards in their search text.

Reality: SEARCH supports wildcards '?' and '*', but FIND does not.

Why it matters: Not knowing this limits your ability to perform flexible pattern searches with SEARCH.

Quick: If FIND does not find the text, does it return 0 or an error? Commit your answer.

Common Belief: FIND returns 0 if the text is not found.

Reality: FIND returns a #VALUE! error when the text is missing.

Why it matters: Assuming 0 leads to incorrect formulas and crashes if error handling is missing.

Quick: Does SEARCH always find the first occurrence of text? Commit yes or no.

Common Belief: SEARCH can find all occurrences of text at once.

Reality: SEARCH only finds the first occurrence; to find others, you must adjust start_num.

Why it matters: Expecting multiple matches at once causes confusion and incorrect formula design.

Expert Zone

1

FIND and SEARCH return the position of the first character of the found text, not the whole substring length.

2

SEARCH's wildcard support can cause unexpected matches if wildcards are used unintentionally.

3

Using start_num greater than the text length returns an error, so bounds checking is important in dynamic formulas.

When NOT to use

Avoid FIND and SEARCH when you need to find multiple occurrences at once or perform complex pattern matching; use FILTERXML, REGEX functions (in Excel 365), or Power Query instead.

Production Patterns

Professionals use FIND/SEARCH combined with MID/LEFT/RIGHT to parse emails, extract codes, or clean data. Wrapping them in IFERROR prevents crashes. They also use SEARCH with wildcards for flexible validation and conditional formatting.

Connections

Regular Expressions

SEARCH's wildcard pattern matching is a simplified form of regular expressions.

Understanding SEARCH wildcards helps grasp the basics of pattern matching before learning full regex.

String Indexing in Programming

FIND and SEARCH return character positions similar to string index functions in programming languages.

Knowing how string indexing works in code clarifies how these functions count characters starting at 1.

Text Search in Databases

FIND and SEARCH are like SQL's LIKE and POSITION functions for searching text within fields.

Recognizing this connection helps when moving between spreadsheet and database text operations.

Common Pitfalls

#1 Using FIND expecting case-insensitive search.

Wrong approach:

FIND("a", "Apple")

Correct approach:

SEARCH("a", "Apple")

Root cause:

Misunderstanding that FIND is case-sensitive causes missed matches.

#2 Not handling errors when text is missing.

Wrong approach:

FIND("x", "Banana")

Correct approach:

IFERROR(FIND("x", "Banana"), 0)

Root cause:

Ignoring that FIND returns errors breaks formulas when text is absent.

#3 Using wildcards with FIND expecting them to work.

Wrong approach:

FIND("b?n", "Banana")

Correct approach:

SEARCH("b?n", "Banana")

Root cause:

Assuming FIND supports wildcards leads to errors or wrong results.

Key Takeaways

FIND and SEARCH locate the position of text inside another text string, returning the starting character number.

FIND is case-sensitive and does not support wildcards; SEARCH is case-insensitive and supports '?' and '*' wildcards.

Both functions return errors if the text is not found, so error handling is important for stable formulas.

Using the optional start_num argument lets you find later occurrences of text, enabling more complex searches.

Combining FIND or SEARCH with text extraction functions like MID unlocks powerful text parsing capabilities.

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