FIND 與 SEARCH：Excel 和 Google Sheets 該用哪一個

快速解答

FIND 區分大小寫，並把 * 與 ? 當作普通字元。SEARCH 不區分大小寫，且把 * 與 ? 當作萬用字元。兩者在找不到時都返回 #VALUE!，不是 #N/A。本次對 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，因為單獨的星號會從首字元匹配整串。

邊界與相容性

可選的第三個引數是起始位置。Banana 中字母 a 在第 2、4 位；起始值為 2 仍得到 2，起始值為 3 才得到第二個 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