FIND and SEARCH in Google Sheets - Deep Dive

Overview - FIND and SEARCH

What is it?

FIND and SEARCH are two functions in Google Sheets used to locate the position of a specific text within another text. They return the number of the character where the searched text starts. FIND is case-sensitive, meaning it distinguishes uppercase and lowercase letters, while SEARCH is not case-sensitive. Both help you quickly find where a word or letter appears inside a cell.

Why it matters

Without FIND and SEARCH, you would have to manually look through text to find where something appears, which is slow and error-prone. These functions let you automate text searching, making it easy to extract or analyze parts of data. This saves time and reduces mistakes, especially when working with large spreadsheets or complex text data.

Where it fits

Before learning FIND and SEARCH, you should understand basic text handling in spreadsheets, like entering text and simple formulas. After mastering these, you can learn related functions like LEFT, RIGHT, MID for extracting text, and IF or ISNUMBER to build conditional logic based on search results.

Mental Model

Core Idea

FIND and SEARCH tell you exactly where a piece of text starts inside another text, like a GPS coordinate for words.

Think of it like...

Imagine you have a book and want to find the page number where a certain word first appears. FIND and SEARCH are like a quick index that tells you the exact page number so you don’t have to flip through every page.

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

Build-Up - 7 Steps

1

Foundation

Basic usage of FIND function

Concept: Learn how FIND locates text and returns its starting position, considering case sensitivity.

Syntax: FIND(search_for, text_to_search, [starting_at]) - search_for: the text you want to find. - text_to_search: where you look for it. - starting_at: optional, where to start searching (default is 1). Example: =FIND("a", "Banana") returns 2 because 'a' first appears at the second character. Note: FIND is case-sensitive, so =FIND("A", "Banana") returns an error because uppercase 'A' is not found.

Result

Returns the position number of the first exact match of the search text, or an error if not found.

Understanding that FIND is case-sensitive helps you know when it will find matches and when it won’t, preventing unexpected errors.

2

Foundation

Basic usage of SEARCH function

Concept: Learn how SEARCH works like FIND but ignores case differences.

Syntax: SEARCH(search_for, text_to_search, [starting_at]) Example: =SEARCH("a", "Banana") returns 2. Example: =SEARCH("A", "Banana") also returns 2 because SEARCH ignores case. SEARCH is useful when you want to find text regardless of uppercase or lowercase letters.

Result

Returns the position number of the first match ignoring case, or an error if not found.

Knowing SEARCH ignores case lets you find text more flexibly, especially when text case varies.

3

Intermediate

Using the optional start position

🤔Before reading on: If you search for 'a' in 'Banana' starting at position 3, do you think it finds the first or second 'a'? Commit to your answer.

Concept: Both FIND and SEARCH let you choose where to start searching inside the text.

Example: =FIND("a", "Banana", 3) starts looking from the third character. In 'Banana', the first 'a' is at position 2, but starting at 3 skips it and finds the next 'a' at position 4. This helps find multiple occurrences by searching after the last found position.

Result

Returns the position of the next occurrence after the start position.

Understanding the start position parameter allows you to find repeated text occurrences step-by-step.

4

Intermediate

Handling errors when text is not found

🤔Before reading on: What do you think happens if FIND or SEARCH can’t find the text? Does it return 0, -1, or an error? Commit to your answer.

Concept: FIND and SEARCH return an error if the text is not found, which can break your formulas unless handled.

Example: =FIND("x", "Banana") returns #VALUE! error because 'x' is not in 'Banana'. To avoid errors, wrap with IFERROR: =IFERROR(FIND("x", "Banana"), "Not found") returns 'Not found'. This keeps your sheet clean and prevents formula breaks.

Result

Prevents errors from stopping your calculations and allows custom messages or alternative actions.

Knowing how to handle errors makes your spreadsheets more robust and user-friendly.

5

Intermediate

Difference between FIND and SEARCH functions

🤔Before reading on: Which function do you think is case-sensitive, FIND or SEARCH? Commit to your answer.

Concept: FIND is case-sensitive; SEARCH is not. This affects which matches they find.

Example: =FIND("A", "Banana") returns error; =SEARCH("A", "Banana") returns 2. Use FIND when case matters, like passwords or codes. Use SEARCH when case doesn’t matter, like searching words in sentences.

Result

Choosing the right function avoids unexpected errors or missed matches.

Understanding this difference helps you pick the right tool for your text search needs.

6

Advanced

Finding multiple occurrences with formulas

🤔Before reading on: Can you find the second or third occurrence of a letter using FIND or SEARCH alone? Commit to your answer.

Concept: By combining FIND or SEARCH with the start position, you can locate multiple occurrences of the same text.

Example: To find the second 'a' in 'Banana': =FIND("a", "Banana") → 2 (first 'a') =FIND("a", "Banana", 3) → 4 (second 'a') =FIND("a", "Banana", 5) → 6 (third 'a') This technique helps extract or analyze repeated patterns.

Result

You can pinpoint each occurrence’s position one by one.

Knowing how to chain searches unlocks powerful text analysis capabilities.

7

Expert

Using FIND and SEARCH in complex text formulas

🤔Before reading on: Do you think FIND and SEARCH can be combined with other functions to extract or manipulate text dynamically? Commit to your answer.

Concept: FIND and SEARCH are often combined with LEFT, RIGHT, MID, LEN, and IF to extract or conditionally process text based on position.

Example: Extract text before a space: =LEFT(A1, FIND(" ", A1) - 1) This gets the first word from a sentence. Example: Conditional check if text exists: =IF(ISNUMBER(SEARCH("apple", A1)), "Found", "Not found") This checks if 'apple' is anywhere in the text, ignoring case. These combinations create dynamic, powerful text processing formulas.

Result

Enables advanced text extraction, validation, and conditional logic in spreadsheets.

Mastering these combinations transforms simple search into versatile text manipulation tools.

Under the Hood

FIND and SEARCH scan the text from left to right, character by character, comparing each segment to the search text. FIND compares characters exactly, including case, while SEARCH converts both texts to lowercase internally to ignore case differences. When a match is found, they return the position of the first character of the match. If no match is found, they return an error. The optional start position shifts where scanning begins, allowing repeated searches.

Why designed this way?

These functions were designed to provide simple, fast ways to locate text within strings, a common need in data processing. FIND’s case sensitivity supports precise matches, important in codes or case-sensitive data. SEARCH’s case insensitivity supports flexible searches in natural language. The start position parameter allows repeated searches without complex looping, fitting spreadsheet formula constraints.

Text to search: "Banana"
Positions:      1 2 3 4 5 6
Search for 'a':  │ ● │ ● │ ●
FIND scans each position:
1: 'B' vs 'a' no
2: 'a' vs 'a' yes → return 2
SEARCH converts to lowercase:
'banana' vs 'a' matches at 2
Start position shifts scan start → e.g. start=3 skips first 'a'

Myth Busters - 4 Common Misconceptions

Quick: Does FIND find text regardless of uppercase or lowercase letters? Commit to yes or no.

Common Belief: FIND ignores case and finds text no matter the letter case.

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

Why it matters: Using FIND without considering case can cause unexpected errors or missed matches, breaking formulas.

Quick: If SEARCH can’t find the text, does it return 0 or an error? Commit to your answer.

Common Belief: SEARCH returns 0 when the text is not found.

Reality: SEARCH returns a #VALUE! error if the text is not found.

Why it matters: Not handling this error can cause your spreadsheet to show errors and stop calculations.

Quick: Can you use FIND or SEARCH to find multiple occurrences in one formula without extra steps? Commit to yes or no.

Common Belief: FIND and SEARCH can directly return all positions of multiple occurrences at once.

Reality: FIND and SEARCH only return the first occurrence position; to find others, you must use the start position parameter repeatedly.

Why it matters: Expecting multiple results from one call leads to confusion and incorrect formulas.

Quick: Does the start position parameter in FIND and SEARCH count from zero or one? Commit to your answer.

Common Belief: The start position counts from zero, like in many programming languages.

Reality: The start position counts from 1, the first character of the text.

Why it matters: Using zero or incorrect start positions causes errors or wrong results.

Expert Zone

1

FIND and SEARCH treat spaces and special characters as normal characters, so searching for spaces or punctuation works the same as letters.

2

SEARCH supports wildcard characters like '?' and '*', allowing pattern matching, while FIND does not support wildcards.

3

When used inside array formulas or combined with REGEX functions, FIND and SEARCH can be part of powerful text filtering and extraction pipelines.

When NOT to use

Avoid FIND and SEARCH when you need to match complex patterns or multiple variations; instead, use REGEXMATCH, REGEXEXTRACT, or REGEXREPLACE for pattern-based text processing. Also, for case-insensitive exact matches, SEARCH is better; for case-sensitive, FIND is preferred.

Production Patterns

Professionals use FIND and SEARCH combined with IF and ISNUMBER to check if text exists and branch logic accordingly. They also use the start position parameter in loops or array formulas to find multiple occurrences. In data cleaning, these functions help extract substrings before or after certain markers dynamically.

Connections

Regular Expressions (Regex)

Builds-on

Understanding FIND and SEARCH prepares you for regex functions, which offer more powerful and flexible text searching and pattern matching.

String Indexing in Programming

Same pattern

FIND and SEARCH mimic the concept of string indexing in programming languages, helping bridge spreadsheet skills with coding.

Library Book Indexing

Analogy to real-world indexing

Knowing how FIND and SEARCH locate text is like understanding how a library index points to where information is stored, improving your mental model of data lookup.

Common Pitfalls

#1 Using FIND when case does not match causes errors.

Wrong approach:

=FIND("A", "Banana")

Correct approach:

=SEARCH("A", "Banana")

Root cause:

Misunderstanding that FIND is case-sensitive and will error if case differs.

#2 Not handling errors when text is missing breaks formulas.

Wrong approach:

=FIND("x", "Banana")

Correct approach:

=IFERROR(FIND("x", "Banana"), "Not found")

Root cause:

Ignoring that FIND and SEARCH return errors when text is not found.

#3 Starting search at zero instead of one causes errors.

Wrong approach:

=FIND("a", "Banana", 0)

Correct approach:

=FIND("a", "Banana", 1)

Root cause:

Confusing spreadsheet indexing (1-based) with programming indexing (0-based).

Key Takeaways

FIND and SEARCH locate the position of text inside other text, with FIND being case-sensitive and SEARCH case-insensitive.

Both functions return the position of the first match or an error if not found, so error handling is important.

The optional start position lets you find multiple occurrences by searching after previous matches.

Combining FIND or SEARCH with other text functions enables powerful text extraction and conditional logic.

Understanding their behavior and differences prevents common errors and unlocks advanced text processing in spreadsheets.

Practice

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

easy

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

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

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

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

Solution

1. Step 1: Understand the behavior of FIND

   The FIND function looks for text inside another text and cares about uppercase and lowercase letters.

2. Step 2: Understand the behavior of SEARCH

   The SEARCH function also looks for text but ignores case differences.

3. Final Answer:

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

4. Quick Check:

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

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

Common Mistakes:

• Thinking SEARCH is case-sensitive
• Confusing FIND with SEARCH output types
• Believing FIND works only with numbers

2. Which of the following is the correct syntax to find the position of "cat" in cell A1 using the FIND function?

easy

A. =FIND("cat")

B. =FIND(A1, "cat")

C. =FIND("cat", A1)

D. =FIND(A1)

Solution

1. Step 1: Recall FIND function syntax

   The syntax is =FIND(search_for, text_to_search_in). So the text to find comes first, then the cell.

2. Step 2: Match syntax with options

   =FIND("cat", A1) matches the correct syntax: =FIND("cat", A1). Others have wrong order or missing arguments.

3. Final Answer:

   =FIND("cat", A1) -> Option C

4. Quick Check:

   Correct syntax = =FIND("cat", A1) [OK]

Hint: Remember FIND needs text to find first, then where to find [OK]

Common Mistakes:

• Swapping the order of arguments
• Omitting required arguments
• Using only one argument

3. Given cell A1 contains the text "Hello World", what will be the result of =SEARCH("world", A1)?

medium

A. 7

B. 8

C. #VALUE! error

D. 6

Solution

1. Step 1: Understand SEARCH ignores case

   The text "world" is found in "Hello World" starting at the 7th character (space is 6th).

2. Step 2: Calculate position

   Counting characters: H(1) e(2) l(3) l(4) o(5) space(6) W(7) o(8) r(9) l(10) d(11). SEARCH finds "world" at position 7.

3. Final Answer:

   7 -> Option A

4. Quick Check:

   SEARCH ignores case, position 7 = 7 [OK]

Hint: SEARCH ignores case; count characters including spaces [OK]

Common Mistakes:

• Assuming case sensitivity causes error
• Counting position starting at 0 instead of 1
• Confusing FIND and SEARCH behavior

4. You used the formula =FIND("apple", B2) but it returns an error. What is the most likely reason?

medium

A. FIND cannot search for words longer than 5 characters.

B. The text "apple" is not found exactly with matching case in B2.

C. B2 contains a number, and FIND only works with text.

D. The formula syntax is incorrect; FIND needs three arguments.

Solution

1. Step 1: Check FIND function behavior

   FIND is case-sensitive and returns an error if the text is not found exactly.

2. Step 2: Analyze possible causes

   If "apple" with exact lowercase letters is not in B2, FIND returns an error. Syntax is correct with two arguments, and FIND works with text regardless of length.

3. Final Answer:

   The text "apple" is not found exactly with matching case in B2. -> Option B

4. Quick Check:

   Case-sensitive search missing text = The text "apple" is not found exactly with matching case in B2. [OK]

Hint: Check exact case match when FIND returns error [OK]

Common Mistakes:

• Thinking FIND needs three arguments
• Assuming FIND works only with text, not numbers
• Believing FIND limits word length

5. You want to find the position of the second occurrence of "cat" in cell A1 containing "scatter cat" using FIND. Which formula will correctly find it?

hard

A. =SEARCH("cat", A1, 2)

B. =FIND("cat", A1) + FIND("cat", A1)

C. =FIND("cat", A1, 2)

D. =FIND("cat", A1, FIND("cat", A1) + 1)

Solution

1. Step 1: Understand FIND's start position parameter

   FIND can start searching from a specific position. To find the second occurrence, start after the first occurrence.

2. Step 2: Use nested FIND to locate second occurrence

   First FIND("cat", A1) finds position of first "cat". Adding 1 moves start to next character. Then FIND("cat", A1, first_position + 1) finds second occurrence.

3. Final Answer:

   =FIND("cat", A1, FIND("cat", A1) + 1) -> Option D

4. Quick Check:

   Nested FIND with start position = =FIND("cat", A1, FIND("cat", A1) + 1) [OK]

Hint: Use FIND inside FIND to locate second occurrence [OK]

Common Mistakes:

• Adding FIND results instead of nesting
• Using SEARCH without case consideration
• Starting search at fixed position 2 instead of after first find