FIND と SEARCH：Excel と Google Sheets での使い分け

要点

FIND は大文字と小文字を区別し、* と ? を通常の文字として扱います。SEARCH は大文字と小文字を区別せず、* と ? をワイルドカードにします。どちらも見つからないときは #N/A ではなく #VALUE! を返します。Apple での結果は、FIND の小文字 a が #VALUE!、SEARCH の小文字 a が 1、FIND の大文字 A が 1 でした。

=FIND("a","Apple")     → #VALUE!
=SEARCH("a","Apple")   → 1
=FIND("A","Apple")     → 1
=FIND("p*e","Apple")   → #VALUE!
=SEARCH("p*e","Apple") → 2
=FIND("z","Apple")     → #VALUE!

確認のポイント

SEARCH に p*e を指定すると Apple では 2 が返り、FIND に同じ文字を指定すると #VALUE! です。SEARCH で本物のアスタリスクを探すときは前にチルダを付けます。A*B では、FIND の * とエスケープ付き SEARCH はどちらも 2、裸の SEARCH の * は 1 でした。裸の * は先頭から文字列全体に一致します。

注意点と互換性

省略可能な第3引数は開始位置です。Banana の a は 2 と 4 にあり、開始位置 2 ではまだ 2、開始位置 3 で初めて 2 つ目の a（位置 4）になります。見つからないとき、IFERROR は FIND を not found に変えましたが、IFNA は #VALUE! のままでした。

英語の詳細例・数式・著者メモ

Quick answer: Use FIND when the case has to match. Use SEARCH when it should not, or when you need * and ? as wildcards. Both functions return #VALUE! if the text is not there, not #N/A, so IFNA will not catch a miss.

=FIND("a","Apple")     → #VALUE!
=SEARCH("a","Apple")   → 1
=FIND("A","Apple")     → 1
=FIND("p*e","Apple")   → #VALUE!
=SEARCH("p*e","Apple") → 2
=FIND("z","Apple")     → #VALUE!

The number is the starting position of the first match, counting from 1. LEN("Apple") in the same file was 5, so a result of 1 is the first character and a result of 2 is the p.

Tested in: Live formulas in Google Sheets, November 12, 2026. Microsoft’s FIND and SEARCH pages are cited for Excel behavior that was not re-tested in a desktop Excel workbook on this date.

Which versions this applies to

FIND and SEARCH exist in current Google Sheets (tested) and in Excel. The case and wildcard differences below were measured in Sheets. Microsoft documents the same split: FIND is case-sensitive and does not support wildcards; SEARCH is case-insensitive and does.

Case is the first decision

The source text in these tests was Apple.

=FIND("a","Apple") returned #VALUE!. =SEARCH("a","Apple") returned 1. The capital A at the start of Apple does not match a lowercase a for FIND. SEARCH treats them as the same letter.

=FIND("A","Apple") returned 1, as did =SEARCH("A","Apple"). When the case already matches, both functions agree.

The same pattern showed up in a longer string. =FIND("pie","Apple Pie") returned #VALUE! because the letters in the cell are Pie, not pie. =SEARCH("PIE","Apple Pie") returned 7, which is where Pie starts (positions 1–6 are Apple and a space).

=FIND("apple","Apple") was #VALUE!. =SEARCH("apple","Apple") was 1.

If you are looking for a code, a SKU, or anything else where a and A must not be treated as equal, use FIND. If you are searching a sentence or a name that may have been typed in mixed case, SEARCH is the one that actually finds it.

Wildcards work in SEARCH, not in FIND

=SEARCH("p*e","Apple") returned 2. The pattern means p, then any characters, then e, so it matches pple starting at the first p. =FIND("p*e","Apple") returned #VALUE! because FIND looks for a literal asterisk between p and e, and Apple does not contain that character sequence.

=SEARCH("?pple","Apple") returned 1. The ? stands for a single character, here the leading A. =FIND("?pple","Apple") returned #VALUE!.

=SEARCH("A*e","Apple") returned 1.

An asterisk you meant as a character, not as a wildcard, has to be escaped in SEARCH. In A*B, =FIND("*","A*B") returned 2, the position of the star. =SEARCH("~*","A*B") also returned 2. =SEARCH("*","A*B") returned 1, because a bare * matches the whole string from the first character. If you forget the tilde, SEARCH will tell you the text was found even when you were looking for a literal star.

start_num skips earlier matches

The optional third argument is the position to start from, still counted from 1.

In Banana, the letter a appears at positions 2 and 4. =FIND("a","Banana",2) and =SEARCH("a","Banana",2) both returned 2, because start_num 2 still includes the first a. =FIND("a","Banana",3) and =SEARCH("a","Banana",3) both returned 4, the second a.

This is useful for walking a string: find the first space, then call FIND again with start_num one past that position to get the next word break. =FIND(" ","Ada Lovelace") returned 4 in this file, the space between Ada and Lovelace.

A miss is #VALUE!, not #N/A

=FIND("z","Apple") and =SEARCH("z","Apple") both returned #VALUE!. That is the same error you get from adding text to a number, which is why a wrapper has to be chosen carefully.

=IFERROR(FIND("z","Apple"),"not found") returned the text not found. =IFNA(FIND("z","Apple"),"not found") stayed #VALUE!, because IFNA only replaces #N/A. This is the same result recorded in the IFERROR vs IFNA and #VALUE! error tests in this workbook.

If you want a blank or a message when a character is missing, use IFERROR, or test with ISNUMBER(FIND(...)). Do not use IFNA around FIND or SEARCH.

When to use which

Exact case, no wildcards: FIND.
Ignore case, or use * and ?: SEARCH.
You need a literal * or ? inside SEARCH: prefix it with ~.
You want to leave a miss visible while debugging: leave the function bare and look for #VALUE!.
You want a fallback when it is missing: IFERROR, not IFNA.

For splitting names on a space, FIND is often enough if you first TRIM the cell and you do not care about odd capitalization of the delimiter. For a case-insensitive search in a comment or a product description, start with SEARCH.

Excel and Google Sheets differences

The case and wildcard rules above are the ones Microsoft documents for Excel, and they held in this Google Sheets file. Argument separators may be semicolons in some European Excel locales; the tests here used commas. I did not re-run the same grid in desktop Excel on this date.

Common mistakes

Using FIND on mixed-case sentences and treating #VALUE! as “the word is not there” when only the case is different.
Putting * into FIND and expecting a wildcard.
Putting a bare * into SEARCH when you wanted the asterisk character.
Wrapping FIND in IFNA and wondering why a miss is still #VALUE!.
Starting the second search at the same position as the first match, then being surprised that you get the same position back.

FAQ

Is SEARCH slower than FIND?

Not in any way this test tried to measure. Pick the one whose rules you want. For a short cell, the difference is not the reason a formula is slow.

Does FIND work on numbers?

Both functions look at text. If the cell is a true number, the spreadsheet may coerce it, but you should not rely on that for IDs with leading zeros. Convert or format deliberately; see numbers stored as text.

Can I make FIND ignore case without switching to SEARCH?

You can run both sides through UPPER or LOWER, then FIND. That is more typing than SEARCH, and it still will not give you wildcards.

What does a result of 1 mean?

The match starts at the first character. It does not mean “true.” Use ISNUMBER(...) if you need a TRUE/FALSE test.

Why did SEARCH("",“AB”) return 1, not 2?

A bare asterisk is a wildcard for any sequence, including the whole cell, so the match starts at position 1. Use ~* to find the actual star, which was at position 2 in this test.

Live-test findings

All positions and errors above come from one Google Sheets file dated November 12, 2026, using the source strings Apple, Apple Pie, Banana, Ada Lovelace, and A*B. FIND missed lowercase a in Apple and the wildcard patterns. SEARCH found those and treated * as a wildcard unless it was written as ~*. A missing letter was #VALUE! for both; IFERROR replaced it and IFNA did not. start_num 3, not 2, was required to reach the second a in Banana.

Sources

Microsoft Support: FIND, FINDB functions: https://support.microsoft.com/en-us/office/find-findb-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628
Microsoft Support: SEARCH, SEARCHB functions: https://support.microsoft.com/en-us/office/search-searchb-functions-9ab04538-0e55-4719-a70e-b04c904b613b
Google Docs Editors Help: FIND, SEARCH: https://support.google.com/docs/answer/3094126, https://support.google.com/docs/answer/3094154