FIND and SEARCH in Google Sheets - Step-by-Step Guide

Introduction

FIND and SEARCH help you locate where a specific word or letter appears inside a text. They tell you the position number of the first match. This is useful when you want to extract or check parts of text in your spreadsheet.

When you want to find the position of a word inside a sentence to extract it later.

When you need to check if a certain letter or word exists in a cell and where it starts.

When you want to split text based on where a character like a comma or space appears.

When you want to compare text parts by locating specific characters.

When you want to clean or organize data by finding patterns inside text.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

Formula is entered but not yet executed

💡 Start formulas with an equal sign (=)

Step 3: Type

- formula bar

Formula shows the position of the search text

💡 For FIND, type: =FIND("text_to_find", A1) where A1 has your text

Step 4: Press

- Enter key

Cell shows the number where the text starts inside the target cell

Step 5: Repeat

- for SEARCH function

SEARCH works like FIND but ignores uppercase or lowercase differences

💡 Type: =SEARCH("text_to_find", A1)

Before vs After

Before

Cell A1 contains the text: "Hello World"

After

Using =FIND("W", A1) returns 7 because W is the 7th character

Settings Reference

Case sensitivity

📍 Function choice: FIND or SEARCH

Choose if you want to match exact letter case or ignore it

Default: FIND is case sensitive

Start position

📍 Third argument in FIND or SEARCH formula

Set where to start looking inside the text

Default: 1

Common Mistakes

Using FIND when you want to ignore letter case

FIND is case sensitive and will not find 'w' if you search for 'W'

Use SEARCH instead to find text regardless of uppercase or lowercase

Not putting the text to find inside quotes

Google Sheets treats unquoted text as a cell reference or error

Always put the text you want to find inside double quotes, like "text"

Searching for text that does not exist without handling errors

FIND and SEARCH return an error if text is not found, which breaks formulas

Use IFERROR to handle missing text, 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 ignores case differences.

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

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