Excel FIND vs SEARCH: case sensitivity and wildcards

Search a column of product names for “cat” and Excel happily returns hits inside “catalog,” “concatenate,” and “location.” That is already the first gotcha with text lookups. The next one is that Excel ships two nearly identical hunters — FIND and SEARCH — and picking the wrong one either skips matches you wanted or grabs ones you didn’t. The split comes down to two behaviors: case sensitivity and wildcard support. Learn those two axes and every “why did this return #VALUE!” moment stops feeling random. What follows walks through the syntax of both, the four decisions that separate them, and the patterns that make them useful once you are past the basics.

Why Excel ships two almost-identical text hunters

Both functions take the same arguments — a needle, a haystack, and an optional starting position — and both return the character position where the first match begins. Both return #VALUE! when nothing matches. If they behaved identically, the second one would be dead weight; the fact that Microsoft keeps both means the differences show up in daily use.

FIND is the strict one. It respects case and treats every character as a literal, so * is an asterisk and ? is a question mark. SEARCH is the forgiving one. It ignores case and interprets * and ? as wildcards that match any run of characters or any single character. That is the whole split.

The rest of the differences flow from those two axes. Everything else — the way you chain them to find the third slash in a URL, the way you pair them with LEFT to grab a domain from an email, the way SEARCH occasionally matches too much — is a consequence of case rules and wildcards.

FIND: exact, case-sensitive, no wildcards

Reach for FIND whenever case carries meaning. Product codes where SKU-A100 and SKU-a100 are two different SKUs. File paths on case-sensitive systems. Human names where the capitalisation is the whole point. In every one of those, matching “abc” against “ABC” is a bug, not a convenience.

=FIND(find_text, within_text, [start_num])

The three arguments read left to right: the text you want to locate, the string being searched, and an optional 1-based character position to start from. The default start is 1. Any wildcard in find_text is treated as its literal character, so FIND("*", A2) genuinely hunts for an asterisk.

Row 2 lands on position 4 because the lowercase e first appears there — the capital E at position 1 is a different character to FIND. Row 4 returns an error because the SKU has a lowercase a and the search string is uppercase. That is the behavior you want when case is data.

SEARCH: forgiving, with wildcards

Reach for SEARCH whenever the input is messy human data — invoices with an optional revision suffix, email addresses in three shapes, part numbers where a middle letter varies. Case is ignored, and two wildcards work inside find_text: ? matches any single character and * matches any run of characters, including zero.

=SEARCH(find_text, within_text, [start_num])

Argument order and defaults are identical to FIND. Everything different lives in the matching rules.

Row 2 lands on position 1 because SEARCH reads case-insensitively — the capital E counts as a match for the lowercase target. Row 3 uses four ? wildcards to match any four characters, catching every 4-digit invoice year. Row 4 uses * to skip whatever domain sits between the at-sign and the top-level .io.

Head to head: which one to pick

Boil the differences to a small comparison and the pick becomes automatic.

The rule collapses to three lines. Pick FIND when case matters, or when the target string might itself contain a literal * or ?. Pick SEARCH when case does not matter, or when your pattern actually needs a wildcard. Everything else — start position, error handling, extraction — behaves the same way in both.

Skip past earlier matches with start_num

The start_num argument exists so you can leap over matches you have already counted. Nest one call inside another and the outer call begins its hunt from just after the previous hit. That is how you get to the second dash in a product code or the third slash in a URL.

Take a file path like orders/2026/invoice-1042.pdf. It has two slashes and one dot, and you want the piece after the last slash. Find the second slash, then take everything to the right of it.

Row 3 is the important line: the inner FIND lands on position 7, the outer starts from position 8, and the answer is position 12 — the second slash. Row 4 wraps the whole thing in MID so it returns the filename directly. If you use this pattern often, giving the inner call a name with the LET function keeps the outer formula readable.

Slice text before or after a delimiter

Pair FIND or SEARCH with LEFT, RIGHT, or MID and you have Excel’s classic string slicer. Grab the username from an email with one, the domain with the other:

=LEFT(A2, FIND("@", A2) - 1)
=RIGHT(A2, LEN(A2) - FIND("@", A2))

The first call finds the at-sign, subtracts one so the character itself is excluded, and takes that many characters from the left. The second calculates the length of everything after the at-sign and lifts it off the right. The pattern generalises to any delimiter: change "@" for ".", "-", or a space and the same shape works.

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

=TEXTBEFORE(A2, "@")

If your users are on modern Excel, TEXTBEFORE and TEXTAFTER handle the common extraction cases without the nesting. The FIND/SEARCH pair still matters for older builds, and it still handles patterns the newer functions do not touch — anything that needs a wildcard or a start position calculated on the fly.

Wildcards, tildes, and the #VALUE! trap

Two rough edges show up once SEARCH is a daily habit. Both are easy to fix once you have seen them.

Escape literal ? and * with a tilde

Say a product code genuinely contains an asterisk — something like MODEL-A*01. Running =SEARCH("A*", B2) will not find the literal A*; it matches “A followed by anything,” which fires on the very first A in the string. Escape the wildcard by putting a tilde in front of it. See the official Microsoft reference for FIND and FINDB for the byte-count sibling functions.

=SEARCH("A~*", B2)
=SEARCH("code~?", B2)

The tilde is invisible to the match — it just tells SEARCH to treat the following character as literal. If you have no wildcards to escape and no case-insensitivity to preserve, FIND sidesteps the whole issue: it never interprets * or ? as anything but themselves.

Wrap the miss in IFERROR

Both functions blow up with #VALUE! when the needle is absent. That is the right behavior when a miss is genuinely unexpected — a broken assumption should surface. When a miss is expected, wrap the call so downstream formulas keep working:

=IFERROR(SEARCH("premium", A2), 0)
=IF(IFERROR(SEARCH("urgent", A2), 0) > 0, "flag", "")

The first formula returns 0 when the word is absent and a positive position when it is present, which lets SUM, COUNTIF, or a helper column key off the boolean. The second wraps that pattern in IF to build a plain-English flag column. For a broader tour of what each Excel error means and when to swallow it, the error troubleshooting guide walks through #VALUE!, #REF!, and the rest.

Wrap-up: two axes, one decision

Case sensitivity picks between FIND and SEARCH; wildcard support seals it when your pattern needs ? or *. Nest them with start_num to walk past earlier matches, combine with LEFT, RIGHT, or MID for the classic slicer, and wrap in IFERROR when a genuine miss deserves a fallback rather than an error. On Excel 365 or 2021, TEXTBEFORE and TEXTAFTER cover the common extraction cases with less nesting. Everywhere else — and for anything wildcard-driven — the FIND/SEARCH pair still earns its place in the toolkit. Start with one lookup you rewrite by hand every month and let the pattern spread from there.

• ✓ Case sensitive? FIND. Case-blind or wildcarded? SEARCH.
• ✓ Need the second or third match? Nest a FIND / SEARCH inside start_num.
• ✓ Extracting text? Pair with LEFT, RIGHT, or MID — or move to TEXTBEFORE / TEXTAFTER on modern Excel.
• ✓ Wrap in IFERROR only when a miss is a real answer, not a hidden bug.