FIND and SEARCH Functions in Google Sheets

The FIND function in Google Sheets returns the position of a character or substring inside a text string. SEARCH does the same thing but ignores case and supports wildcards. The text functions guide covers the full set of text tools, and FIND and SEARCH are the foundation for dynamic text extraction when you combine them with LEFT, RIGHT, and MID.

This guide covers both functions, when to use each, and how to pair them with other formulas for real data tasks.

In This Guide

• FIND Syntax and Parameters
• SEARCH Syntax and Parameters
• FIND vs SEARCH: When to Use Each
• Locate Text Step-by-Step
• Practical Examples
• Wildcard Searches with SEARCH
• Common Errors and How to Fix Them
• Tips and Best Practices
• Related Google Sheets Tutorials
• Frequently Asked Questions

FIND Syntax and Parameters

FIND returns the position of a substring within a text string. It is case-sensitive.

=FIND(search_for, text_to_search, [starting_at])

Example: =FIND("@", "harry.potter@owlmail.com") returns 13. The @ symbol is the 13th character.

SEARCH Syntax and Parameters

SEARCH returns the position of a substring within a text string. It is case-insensitive and supports wildcards.

=SEARCH(search_for, text_to_search, [starting_at])

Example: =SEARCH("potter", "HARRY POTTER") returns 9. SEARCH matches despite the case difference.

Wildcards in SEARCH:

• ? matches any single character
• * matches any sequence of characters

FIND vs SEARCH: When to Use Each

Use FIND when the case of the text matters, like distinguishing between product codes “ABC” and “abc”. Use SEARCH for everything else. Since most text lookups do not require case sensitivity, SEARCH is the more common choice.

Locate Text Step-by-Step

We’ll use the messy contacts table to locate characters in email addresses and names.

The table has 8 contacts with emails in column B. Some emails are lowercase (harry.potter@owlmail.com), some are uppercase (HERMIONE.GRANGER@OWLMAIL.COM), and some are mixed (L.LOVEGOOD@owlmail.COM).

Select cell F2 and enter:

=FIND("@", B2)

For "harry.potter@owlmail.com", FIND returns 13. The @ symbol is at position 13. Copy down through row 9. Every email has an @ symbol, so FIND works on all rows.

Select cell G2 and enter:

=LEFT(B2, FIND("@", B2) - 1)

FIND returns 13. Subtracting 1 gives 12. LEFT returns the first 12 characters: harry.potter. This dynamic extraction works regardless of username length.

Select cell H2 and enter:

=SEARCH("potter", A2)

Cell A2 contains "HARRY POTTER" in all caps. SEARCH returns 9 because it ignores case and finds “POTTER” starting at position 9 (after “HARRY ”). FIND would return an error here because “potter” does not match “POTTER”.

Practical Examples

Example 1: Check If a Cell Contains Text

To check if a cell contains a specific word, wrap SEARCH in ISNUMBER:

=ISNUMBER(SEARCH("weasley", A2))

SEARCH returns a number if it finds the text, or an error if it does not. ISNUMBER converts this to TRUE or FALSE. For " Ronald Weasley ", this returns TRUE. For "HARRY POTTER", it returns FALSE.

Combine with IF to return a label: =IF(ISNUMBER(SEARCH("weasley", A2)), "Weasley family", "Other").

Example 2: Find the Second Occurrence

Use the starting_at argument to skip past the first match:

=FIND(".", B2, FIND(".", B2) + 1)

The inner FIND locates the first period in the email. Adding 1 moves past it. The outer FIND searches from that new position. For "harry.potter@owlmail.com", the first period is at position 6. The second period is at position 21 (in “owlmail.com”). This returns 21.

Example 3: Extract Domain from Email

Combine FIND with MID to get everything after the @ symbol:

=MID(B2, FIND("@", B2) + 1, LEN(B2))

FIND returns 13. Adding 1 starts MID at position 14. LEN provides a count larger than the remaining characters, so MID returns everything from position 14 to the end: owlmail.com.

Wildcard Searches with SEARCH

SEARCH supports two wildcards that FIND does not:

Question mark (?) matches one character:

=SEARCH("??weasley", LOWER(TRIM(A4)))

This finds any two characters followed by “weasley”. For "ronald weasley" (after LOWER and TRIM), the "d " before “weasley” matches the two ? wildcards, and SEARCH returns 6.

Asterisk (*) matches any number of characters:

=SEARCH("h*r", A2)

This finds text that starts with “h” and ends with “r” with any characters in between. For "HARRY POTTER", SEARCH matches “HARRY POTTER” (the h through the final r) and returns 1.

Common Errors and How to Fix Them

#VALUE! Error: Text Not Found

Both FIND and SEARCH return #VALUE! when the search text does not exist in the cell. Wrap in IFERROR to handle missing text: =IFERROR(FIND("@", A2), 0) returns 0 when there is no @ symbol.

#VALUE! Error: Case Mismatch (FIND Only)

FIND is case-sensitive. =FIND("abc", "ABC") returns #VALUE!. Switch to SEARCH if case does not matter, or convert the text first: =FIND("abc", LOWER(A2)).

#VALUE! Error: Starting Position Beyond Text Length

If starting_at is greater than the length of the text, both functions return #VALUE!. Check with LEN first: =IF(LEN(A2) >= 5, FIND("x", A2, 5), "Too short").

Tips and Best Practices

1. Default to SEARCH unless case matters. SEARCH handles both cases and supports wildcards. FIND is only needed when you must distinguish between uppercase and lowercase.

2. Combine with LEFT, MID, or RIGHT for extraction. FIND and SEARCH return position numbers, not the text itself. Use the position as an argument in LEFT, RIGHT, or MID to pull the actual text.

3. Use starting_at to find the Nth occurrence. Nest FIND calls to skip past earlier matches. =FIND(",", A2, FIND(",", A2) + 1) finds the second comma.

4. Wrap in ISNUMBER for TRUE/FALSE checks. =ISNUMBER(SEARCH("text", A2)) is the standard pattern for checking if a cell contains text. It returns TRUE or FALSE without errors.

5. SUBSTITUTE is better for replacing text. FIND and SEARCH locate text. If you need to replace it, use SUBSTITUTE or the REGEX functions instead.

Related Google Sheets Tutorials

• Text Functions in Google Sheets — Full guide to cleaning, extracting, and combining text
• LEFT, RIGHT, MID in Google Sheets — Extract characters using positions from FIND or SEARCH
• SUBSTITUTE Function in Google Sheets — Replace text in formulas instead of locating it
• SPLIT Function in Google Sheets — Separate text at delimiters into columns
• IF Function in Google Sheets — Conditional logic with ISNUMBER and SEARCH

Frequently Asked Questions

What is the difference between FIND and SEARCH in Google Sheets?

FIND is case-sensitive and does not support wildcards. SEARCH is case-insensitive and supports wildcards (? for one character, * for any number). Use FIND when case matters and SEARCH when it does not.

How do I find the position of a character in a cell in Google Sheets?

Use =FIND(character, cell) or =SEARCH(character, cell). Both return the position number of the first occurrence. For example, =FIND("@", B2) returns the position of the @ symbol in an email address.

Can FIND or SEARCH return the position of the second occurrence?

Yes. Use the optional starting_at argument. =FIND(".", B2, FIND(".", B2) + 1) finds the second period by starting the search one position after the first period.

Why does FIND return an error when the text exists in the cell?

FIND is case-sensitive. If you search for “potter” but the cell contains “POTTER”, FIND returns #VALUE! because the cases do not match. Use SEARCH instead for case-insensitive matching.

How do I check if a cell contains specific text in Google Sheets?

Wrap SEARCH in ISNUMBER: =ISNUMBER(SEARCH("text", A2)). This returns TRUE if the text exists anywhere in the cell, FALSE otherwise. SEARCH is case-insensitive, so it matches regardless of capitalization.