---
title: "FIND and SEARCH functions in Google Sheets: how to use them"
canonical: "https://www.owox.com/blog/articles/find-and-search-functions-google-sheets"
updated: "2026-10-05"
---

# FIND and SEARCH functions in Google Sheets: differences, formulas and examples

FIND and SEARCH return the position of a piece of text inside a cell. FIND is case-sensitive; SEARCH ignores case and accepts wildcards. This guide covers both, the formulas built on them for extracting, checking and replacing text, and the #VALUE! error they return when nothing is found.

[Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 10 min read

![FIND and SEARCH functions in Google Sheets: differences, formulas and examples](https://cdn.owox.ai/www/webflow/69f0f35178adb95c2dfcce15_USING-FIND-AND-SEARCH-FUNCTIONS.png/public)

FIND and SEARCH both return the position of a piece of text inside a cell, counted in characters from the left. `=FIND("@", C3)` and `=SEARCH("@", C3)` return 6 for [alice@example.com](mailto:alice@example.com). They differ in two ways:

|  | FIND | SEARCH |
| --- | --- | --- |
| Upper and lower case | Case-sensitive: “a” does not match “A” | Ignores case |
| Wildcards ? and \* | Not supported | Supported |
| Text not found | #VALUE! | #VALUE! |

To look for a value on the sheet by hand, without a formula, press Ctrl + F (Cmd + F on a Mac); see the guide to [find and replace](/blog/articles/how-to-find-and-replace-google-sheets). The rest of this article is about the two functions.

| You need | Formula |
| --- | --- |
| Does the cell contain a word? | `=ISNUMBER(SEARCH("pending", C3))` |
| The text before a character | `=LEFT(C3, SEARCH("@", C3) - 1)` |
| The text after a character | `=RIGHT(C3, LEN(C3) - SEARCH("@", C3))` |
| The position of the second occurrence | `=FIND(".", C3, FIND(".", C3) + 1)` |
| Rows that contain a word | `=FILTER(D3:D12, ISNUMBER(SEARCH("pending", D3:D12)))` |

## Syntax of FIND and SEARCH

> `=FIND(search_for, text_to_search, [starting_at])`

> `=SEARCH(search_for, text_to_search, [starting_at])`

*   **search\_for:** the text to look for.
*   **text\_to\_search:** the cell or text to look in.
*   **starting\_at (optional):** the character position to start from. The default is 1, the first character.

Both return the position of the first match as a number. The position is always counted from the start of the text, also when the search begins later.

### Example of SEARCH

To find the position of the @ sign in an email address:

> `=SEARCH("@", C3)`

![Google Sheets with SEARCH returning the position of a character or substring in text, ignoring case](https://cdn.owox.ai/www/webflow/678703771e817c22528dd21b_60550.png/public)

The position tells you where the username ends and the domain begins, which the extraction formulas below build on. The [SEARCH function](/blog/articles/search-function-google-sheets) has its own guide with more examples.

### Example of FIND

To find the position of the first space in a name:

> `=FIND(" ", B3)`

![Google Sheets with FIND returning the position of a character or substring, respecting case](https://cdn.owox.ai/www/webflow/678703779bbfd34c29fdebcf_60554.png/public)

The first space separates the first name from the last name. For a space or an @ sign, case does not matter, and FIND and SEARCH return the same number.

## FIND is case-sensitive

FIND matches the exact case. Looking for a lowercase “a”:

> `=FIND("a", B5)`

![Google Sheets with FIND locating a lowercase character, showing that FIND is case-sensitive](https://cdn.owox.ai/www/webflow/678703774015f2f9c696cb5d_60568.png/public)

FIND returns the position of the first lowercase “a”. An uppercase “A” does not count, and when the text has no lowercase “a” at all, the result is #VALUE!.

![The result of FIND returning a position when the character is present and an error when it is not](https://cdn.owox.ai/www/webflow/67870377faff833198495aca_60570.png/public)

SEARCH would return a position in every case where the letter appears, in either case. Use FIND when the difference matters, for example for codes where “a” and “A” mean different things.

### Find a word

Both functions look for any sequence of characters, not just single ones:

> `=FIND("review", "The product review was positive")`

![Google Sheets with FIND locating a whole word inside a text string](https://cdn.owox.ai/www/webflow/678703776bb7f2f6f5a260a6_60572.png/public)

The formula returns 13: the word starts at the thirteenth character.

### Start from a later position

The third argument skips the beginning of the text. To look for “a” from the fifth character onward:

> `=FIND("a", B5, 5)`

![Google Sheets with FIND given a starting position so the search begins partway along the text](https://cdn.owox.ai/www/webflow/67870379ffbf811f4b681ce4_60576.png/public)

If there is no “a” from that position on, the result is #VALUE!.

### Find the second occurrence

To find the second period in a text, start the search right after the first one:

> `=FIND(".", C3, FIND(".", C3) + 1)`

*   **FIND(“.”, C3):** the position of the first period.
*   **\+ 1:** the character after it, where the outer FIND starts looking.

![Google Sheets with FIND used to locate the position of the first dot, then the next, to find the nth occurrence](https://cdn.owox.ai/www/webflow/678703764d8a656db3275730_60574.png/public)

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## SEARCH accepts wildcards

In SEARCH, a question mark stands for any single character and an asterisk for any number of characters. `=SEARCH("b?n", "Banana")` returns 1, and `=SEARCH("n*a", "Banana")` returns 3. FIND treats both as ordinary characters: `=FIND("b?n", "banana")` returns #VALUE!.

## Check whether a cell contains text

On their own, FIND and SEARCH return a number or an error. To turn that into TRUE and FALSE, wrap them in ISNUMBER: `=ISNUMBER(SEARCH("pending", C3))`.

### A label with IF

> `=IF(ISERROR(FIND("pending", C3)), "Reviewed", "Needs Attention")`

*   **FIND(“pending”, C3):** looks for the lowercase word “pending”.
*   **ISERROR(…):** TRUE when the word is not found.
*   **IF(…):** returns “Reviewed” when it is not found and “Needs Attention” when it is.

![Google Sheets with FIND wrapped in IF and ISERROR, returning a custom result when “pending” is or is not found](https://cdn.owox.ai/www/webflow/678703771d77a3f59d0d955e_60594.png/public)

Because FIND is case-sensitive, “Pending” with a capital letter is not matched. Replace FIND with SEARCH to catch both. More on conditions in the guide to the [IF function](/blog/articles/if-function-google-sheets).

### A list of matching rows with FILTER

> `=FILTER(D3:D12, IFERROR(SEARCH("pending", D3:D12), FALSE))`

*   **SEARCH(“pending”, D3:D12):** a position for the cells that contain the word, an error for the others.
*   **IFERROR(…, FALSE):** turns the errors into FALSE, so that [FILTER](/blog/articles/google-sheets-filter-function) can use the result as a condition.

![Google Sheets with SEARCH inside FILTER and IFERROR, listing the rows whose text contains “pending”](https://cdn.owox.ai/www/webflow/67870376904306e5f1e39f5e_60580.png/public)

The formula returns the comments that contain “pending”.

### Highlighting with conditional formatting

1\. Select the range to format.

![Google Sheets with a range of cells selected before conditional formatting is applied with SEARCH](https://cdn.owox.ai/www/webflow/678703779c8102103e5d99d8_60560.png/public)

2\. Open **Format > Conditional formatting**.

![The Format menu open on Conditional formatting, in Google Sheets](https://cdn.owox.ai/www/webflow/67870377962f4242085d04f6_60562.png/public)

3\. Under **Format cells if**, choose **Custom formula is** and enter the formula:

> `=SEARCH("pending", $D3)`

![The conditional formatting rule using a custom formula built on SEARCH for the word “pending”](https://cdn.owox.ai/www/webflow/678703777192fe7c30cfc698_60564.png/public)

The dollar sign fixes the column, so the whole row is formatted when column D of that row contains the word. The rule in the screenshot is written as `=SEARCH("pending", D3:D)`, which gives the same result here.

![Google Sheets with rows highlighted where the Comments column contains the word “pending”](https://cdn.owox.ai/www/webflow/6787037600404bd865719dfa_60566.png/public)

### Every cell of a range at once

Inside [ARRAYFORMULA](/blog/articles/array-formulas-google-sheets), one formula returns a position for each cell of a range:

> `=ARRAYFORMULA(FIND("m", B3:B12))`

![Google Sheets with FIND inside ARRAYFORMULA searching every cell in a range for the letter m](https://cdn.owox.ai/www/webflow/67870376a216ac19626af6d0_60598.png/public)

Cells without a lowercase “m” show #VALUE!. Wrap FIND in IFERROR to leave them empty: `=ARRAYFORMULA(IFERROR(FIND("m", B3:B12)))`.

## Extract text with FIND and SEARCH

### The part before a character

> `=LEFT(C3, SEARCH("@", C3, 1) - 1)`

*   **SEARCH(“@”, C3, 1):** the position of the @ sign.
*   **\- 1:** the number of characters before it.
*   **LEFT(C3, …):** that many characters from the start.

![Google Sheets with SEARCH and LEFT extracting the part of an email address before the @ sign](https://cdn.owox.ai/www/webflow/67870377faff833198495ac7_60584.png/public)

The formula returns the username of each email address.

### The part after a character

> `=RIGHT(C3, LEN(C3) - SEARCH("@", C3))`

*   **LEN(C3):** the length of the whole address.
*   **LEN(C3) - SEARCH(“@”, C3):** the number of characters after the @ sign.
*   **RIGHT(C3, …):** that many characters from the end.

![Google Sheets with SEARCH with RIGHT and LEN extracting the part of an email address after the @ sign](https://cdn.owox.ai/www/webflow/6787037703ff0b5c9ab6157b_60586.png/public)

The formula returns the domain. The same pattern with FIND takes the part of a product code after the dash:

> `=RIGHT(C3, LEN(C3) - FIND("-", C3))`

![Google Sheets with FIND with RIGHT and LEN extracting the characters after a found position](https://cdn.owox.ai/www/webflow/67870378032cd7355225f5c0_60592.png/public)

### The first word

> `=MID(C3, 1, FIND(" ", C3) - 1)`

![Google Sheets with FIND and MID extracting the first word of each comment, the characters before the first space](https://cdn.owox.ai/www/webflow/678703772be476831cf9aa93_60590.png/public)

FIND returns the position of the first space, and MID takes the characters before it. `=LEFT(C3, FIND(" ", C3) - 1)` does the same. The guide to [LEFT, RIGHT and MID](/blog/articles/left-right-mid-functions-google-sheets) covers these functions.

### Build a text from the extracted part

> `=CONCATENATE("Hello, ", LEFT(B3, FIND("@", B3) - 1), "! Welcome to our service.")`

![Google Sheets with CONCATENATE building a greeting around text found with FIND](https://cdn.owox.ai/www/webflow/6787037663d0c622ee752978_60604.png/public)

The middle part is the username taken from the email address; [CONCATENATE](/blog/articles/concatenate-and-ampersand-google-sheets) puts the fixed text around it.

## Replace text at a found position

[REPLACE](/blog/articles/replace-function-google-sheets) needs a position and a length, and SEARCH supplies the position. To change “Q1” to “Q2”:

> `=REPLACE(C3, SEARCH("Q1", C3), 2, "Q2")`

*   **SEARCH(“Q1”, C3):** where “Q1” starts.
*   **2:** the number of characters to replace.
*   **“Q2”:** the new text.

![Google Sheets with SEARCH inside REPLACE, swapping a found piece of text such as Q1 for new text](https://cdn.owox.ai/www/webflow/67870377e15f9a0a414c74eb_60588.png/public)

Do not pass the result of FIND to SUBSTITUTE as its fourth argument. That argument is the number of the occurrence to replace, not a position: `=SUBSTITUTE("banana", "a", "@", FIND("a", "banana"))` returns ban@na, because FIND returns 2 and SUBSTITUTE then replaces the second “a”. To replace the first occurrence, use `=SUBSTITUTE(B3, "a", "@", 1)`.

## Errors in FIND and SEARCH

**⚠️ Error:** #VALUE!

✅ **Solution:** This is the only error the two functions return for the data, and it has three causes. The text was not found. With FIND, the text is there but in a different case. Or the starting position is 0, negative, or beyond the end of the text. To show something else instead, use IFERROR: `=IFERROR(FIND("x", B3), 0)`.

**⚠️ Error:** #NAME?

✅ **Solution:** The function name is misspelled, or the text to look for is not in quotes.

**⚠️ Error:** #ERROR! (formula parse error)

✅ **Solution:** A quote or a parenthesis is missing.

**⚠️ Error:** A position is returned, but the wrong one.

✅ **Solution:** Both functions return the first match. If the text appears more than once, use the starting position to move past the earlier ones.

## Related Google Sheets functions

Functions that are often used next to FIND and SEARCH, each with its own guide:

*   [SEARCH](/blog/articles/search-function-google-sheets): The case-insensitive search in detail.
*   [REGEX functions](/blog/articles/regex-functions-google-sheets): Match, extract and replace text by pattern.
*   [SUBSTITUTE](/blog/articles/substitute-function-google-sheets): Replaces one text with another, everywhere or at a given occurrence.
*   [LEFT/RIGHT/MID](/blog/articles/left-right-mid-functions-google-sheets): Extracts a set number of characters from the start, end or middle of a text string.
*   [SPLIT](/blog/articles/how-to-split-cells-in-google-sheets): Cuts a text into columns at a delimiter.
*   [MATCH](/blog/articles/google-sheets-match-function): Returns the position of a value in a range of cells, where FIND returns a position inside one cell.
*   [CHAR](/blog/articles/char-function-google-sheets): Returns the character for a Unicode number, useful for symbols and line breaks.
*   [Wrap text](/blog/articles/how-to-wrap-text-google-sheets): Keeps long text visible inside a cell by breaking it onto several lines.

## Text formulas repair what the export broke

Most formulas in this guide take apart a value that was one field in the system it came from: a domain inside an email address, a region inside a campaign name, a status inside a comment. In the source those are often separate columns. They become one string on the way into the spreadsheet, and FIND and SEARCH are then used to cut it apart again.

The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/find-and-search-functions-google-sheets/screens/1.png/public)

What changes for the person who maintains the sheet:

*   **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
*   **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
*   **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL. When the analyst exposes the parts as columns, the sheet receives them ready to use, and the text functions are left for the cases that really are free text. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

How do I handle errors when using FIND or SEARCH in Google Sheets?

Both functions return #VALUE! when the text is not found. Wrap them in IFERROR to return something else: =IFERROR(FIND("text", A2), "Not found"). To get TRUE or FALSE, use =ISNUMBER(SEARCH("text", A2)).

Can I combine FIND or SEARCH with other formulas in Google Sheets?

Yes. =IF(ISNUMBER(SEARCH("text", A2)), "Found", "Not found") returns a label, =LEFT(A2, FIND("@", A2) - 1) extracts the part before a character, and =FILTER(A2:A, ISNUMBER(SEARCH("text", A2:A))) lists the rows that contain the text.

Can I use FIND or SEARCH across multiple sheets in Google Sheets?

FIND and SEARCH look inside one cell, so they do not search a sheet. To check a range on another tab, add the tab name: =ARRAYFORMULA(IFERROR(SEARCH("text", Sheet2!A2:A20))). To look for a value across the whole file by hand, press Ctrl + F and choose to search all sheets in the find and replace dialog.

How can I perform a case-insensitive search in Google Sheets?

Use SEARCH: =SEARCH("text", A2) finds the text whatever its case and returns its position. SEARCH also accepts the wildcards ? and \*. FIND, by contrast, matches the exact case.

How do I use the FIND function to locate specific text in Google Sheets?

Type =FIND("text", A2), with the text to look for in quotes and the cell to look in as the second argument. The function returns the position of the first match, counted in characters from the left, and #VALUE! when the text is not there. FIND is case-sensitive.

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

The FIND function in Google Sheets is case-sensitive and does not support wildcard characters, while the SEARCH function is case-insensitive and allows the use of wildcards (\* and ?). Both functions return the position of a substring within a text, but suit different needs based on sensitivity and flexibility.

## Who wrote this

![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Google Sheets Tips](/blog/topics/google-sheets-tips)

## Related articles

[Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](/blog/articles/pivot-tables-google-sheets)

[Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: join two columns · October 2, 2026](/blog/articles/query-with-concatenate-google-sheets)

[Google Sheets Tips · QUERY with IMPORTRANGE in Google Sheets: formula, examples · October 2, 2026](/blog/articles/query-with-importrange-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [find and replace](https://www.owox.com/blog/articles/how-to-find-and-replace-google-sheets) — /blog/articles/how-to-find-and-replace-google-sheets.md
- [SEARCH function](https://www.owox.com/blog/articles/search-function-google-sheets) — /blog/articles/search-function-google-sheets.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [IF function](https://www.owox.com/blog/articles/if-function-google-sheets) — /blog/articles/if-function-google-sheets.md
- [FILTER](https://www.owox.com/blog/articles/google-sheets-filter-function) — /blog/articles/google-sheets-filter-function.md
- [ARRAYFORMULA](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md
- [LEFT, RIGHT and MID](https://www.owox.com/blog/articles/left-right-mid-functions-google-sheets) — /blog/articles/left-right-mid-functions-google-sheets.md
- [CONCATENATE](https://www.owox.com/blog/articles/concatenate-and-ampersand-google-sheets) — /blog/articles/concatenate-and-ampersand-google-sheets.md
- [REPLACE](https://www.owox.com/blog/articles/replace-function-google-sheets) — /blog/articles/replace-function-google-sheets.md
- [REGEX functions](https://www.owox.com/blog/articles/regex-functions-google-sheets) — /blog/articles/regex-functions-google-sheets.md
- [SUBSTITUTE](https://www.owox.com/blog/articles/substitute-function-google-sheets) — /blog/articles/substitute-function-google-sheets.md
- [SPLIT](https://www.owox.com/blog/articles/how-to-split-cells-in-google-sheets) — /blog/articles/how-to-split-cells-in-google-sheets.md
- [MATCH](https://www.owox.com/blog/articles/google-sheets-match-function) — /blog/articles/google-sheets-match-function.md
- [CHAR](https://www.owox.com/blog/articles/char-function-google-sheets) — /blog/articles/char-function-google-sheets.md
- [Wrap text](https://www.owox.com/blog/articles/how-to-wrap-text-google-sheets) — /blog/articles/how-to-wrap-text-google-sheets.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md
- [Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](https://www.owox.com/blog/articles/pivot-tables-google-sheets) — /blog/articles/pivot-tables-google-sheets.md
- [Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: join two columns · October 2, 2026](https://www.owox.com/blog/articles/query-with-concatenate-google-sheets) — /blog/articles/query-with-concatenate-google-sheets.md
- [Google Sheets Tips · QUERY with IMPORTRANGE in Google Sheets: formula, examples · October 2, 2026](https://www.owox.com/blog/articles/query-with-importrange-google-sheets) — /blog/articles/query-with-importrange-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md