Cellwise Try it free

Excel guide · 3 min read

FIND vs SEARCH in Excel

Quick answerFIND and SEARCH both return the position of text inside a cell, and both return #VALUE! when it is not there. The difference: FIND is case-sensitive and treats * and ? as ordinary characters, while SEARCH ignores case and supports wildcards. Use SEARCH for "does this cell contain..." checks, and FIND when capital letters matter.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

AB
1SKUSupplier note
2TS-RED-MShips Monday
3HD-Red-LBackorder until May
4TS-NAVY-Sships monday
5CP-red-OSDiscontinued

Case: FIND cares, SEARCH does not

These SKUs came from three suppliers, and each writes the colour differently: RED, Red and red. FIND("Red",...) only matches the exact capitalisation. In HD-Red-L, "Red" starts at character 4.

Run the same FIND on TS-RED-M and you get #VALUE!, because "RED" is not "Red". SEARCH("Red",A2) returns 4 for that cell, and for the other two red SKUs as well.

=FIND("Red",A3)

Result: 4

4: the R of Red is the fourth character of HD-Red-L.

The error when the text is missing

Both functions return #VALUE! when they find nothing. That is fine for a position, but not for a yes/no test, so wrap them in ISNUMBER: any position becomes TRUE, the error becomes FALSE.

Here FIND fails because the case does not match, which is the classic surprise when someone uses FIND for a "contains" check.

=FIND("Red",A2)

Result: #VALUE!

#VALUE!: TS-RED-M contains RED, not Red, and FIND is case-sensitive.

Count cells that contain text: side by side

To count the red SKUs regardless of case, use SEARCH inside ISNUMBER and add up the TRUEs with SUMPRODUCT. Swap SEARCH for FIND and the same formula counts only the exact "Red", which returns 1.

For a simple contains count, =COUNTIF(A2:A5,"*red*") also works and is not case-sensitive. The SEARCH version is worth knowing because you can change it to FIND when case matters, which COUNTIF cannot do.

=SUMPRODUCT(--ISNUMBER(SEARCH("red",A2:A5)))

Result: 3

3 SKUs contain red in any case: TS-RED-M, HD-Red-L and CP-red-OS.

Splitting codes: FIND is the safe default

When you look for a delimiter like a hyphen, case does not apply, so FIND and SEARCH give the same answer. FIND is slightly safer here: if the delimiter is ? or *, SEARCH treats it as a wildcard and matches the wrong thing.

The third argument, start_num, lets you find the second hyphen by starting just after the first. This pulls the colour out of the middle of TS-RED-M.

=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)

Result: RED

RED: the text between the first hyphen (position 3) and the second (position 7).

Wildcards: SEARCH only

In SEARCH, ? matches any single character and * matches any run of characters. =SEARCH("ships*day",B2) finds "Ships Monday" or "ships Friday". FIND would look for a literal asterisk.

To search for an actual ? or * with SEARCH, put a tilde in front: SEARCH("~?",B2). With FIND you can type the character as it is. Most people never need wildcards, so the real deciding question is usually case.

A contains check inside IF

The everyday use: flag rows whose note mentions a word. SEARCH catches "Ships Monday" and "ships monday" alike, which is what you want for free text typed by people.

=IF(ISNUMBER(SEARCH("monday",B4)),"Monday ship","Check")

Result: Monday ship

Monday ship, even though the note is all lowercase.

Common mistakes

Using FIND for a contains test on messy text

People type in any case. Use SEARCH unless you specifically need to tell Red from RED.

Treating the result as TRUE or FALSE

Both return a number or an error. =IF(SEARCH("red",A2),...) breaks when the text is missing. Wrap it: IF(ISNUMBER(SEARCH(...)),...).

Searching for ? or * with SEARCH

They act as wildcards. Use FIND, or escape them in SEARCH with a tilde: "~*".

FAQ

Is FIND or SEARCH faster?

There is no meaningful speed difference. Pick based on whether case matters.

What do FIND and SEARCH return if the text appears twice?

The position of the first match from start_num. To find the second, start one character after the first match, as in FIND("-",A2,FIND("-",A2)+1).

How is FIND in a formula different from Ctrl+F?

Ctrl+F (Home > Find & Select > Find) is a tool for locating cells and is not case-sensitive unless you tick Match case. The FIND function works inside a cell and is always case-sensitive.

Can I search for several words at once?

Not in one call. Combine tests with OR: =OR(ISNUMBER(SEARCH("red",A2)),ISNUMBER(SEARCH("navy",A2))).

Functions used

Related guides

Updated