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