FIND vs SEARCH in Excel: the two differences

FIND and SEARCH both return the position of some text inside a cell. There are only two differences. FIND treats capitals and small letters as different, and SEARCH does not. SEARCH accepts the wildcards ? and *, and FIND does not.

Everything else is the same. Both take what to look for first, then where to look. Both return #VALUE! when the text is not there.

Here is a bank statement export. You want to mark every payment to one shop. The bank writes the shop name in capitals, in small letters and in mixed case.

Difference 1: capitals

Type =FIND("Tesco",A2) and fill it down to row 7. Then do the same with =SEARCH("Tesco",A2).

FIND only finds row 7, where the name is spelled Tesco exactly. Row 2 says TESCO, so FIND gives #VALUE!. SEARCH finds all 3 rows. In row 2 the name starts at character 14. In row 5 it starts at character 1.

Bank exports, form answers and data typed by people mix capitals all the time. For them, SEARCH is the one to use.

Difference 2: wildcards

In SEARCH, a question mark stands for any one character. A star stands for any number of characters. FIND has no wildcards. It looks for a real question mark.

A position is useful inside another function. To take out the invoice number, use =MID(A7,SEARCH("INV-",A7),8). It gives INV-4471.

Check if a cell contains a word

Excel has no CONTAINS function. The usual way is to put SEARCH inside ISNUMBER:

Put it inside IF to write a label:

For one word or another, use OR. =OR(ISNUMBER(SEARCH("tesco",A2)),ISNUMBER(SEARCH("gas",A2))) is TRUE for rows 2, 3, 5 and 7.

To count the rows, you do not need a helper column. =COUNTIF(A2:A7,"*tesco*") gives 3. The stars mean "anything before" and "anything after". Without them, COUNTIF looks for cells that say only tesco, and gives 0.

When FIND is the right one

Sometimes capitals carry meaning. In this export, DD at the start means a direct debit. Try to mark them with SEARCH:

Product codes are the other common case. If AB-1 and ab-1 are different products, use FIND.

The empty search cell

Many people put the search word in a cell, say E1, so they can change it. Then they fill =ISNUMBER(SEARCH($E$1,A2)) down the column.

Clear E1 and every row says TRUE. Both functions find empty text at position 1. So an empty search cell matches everything, and a filter on TRUE shows every row.

Which one to use

Use SEARCH unless capitals carry meaning. It finds a word however people typed it, and it takes wildcards. Use FIND for codes and markers where AB and ab are different things.

If a FIND formula gives #VALUE! and you can see the word, check the capitals first. The #VALUE! article covers the other causes.

Questions people ask

Does SEARCH match whole words only?

No. =ISNUMBER(SEARCH("gas","Vegas trip")) is TRUE, because gas is inside Vegas. To match a whole word, add a space on each side of both texts: =ISNUMBER(SEARCH(" gas "," "&A3&" ")). That is TRUE for row 3. The same test on Vegas trip is FALSE.

How do I find the second space?

Give a start position as the third argument. =FIND(" ",A2,6) starts at character 6 and gives 13. Both functions take this third argument.

Can I use this to split a name?

Yes. The position tells LEFT how many characters to take. The split a full name article shows the full formula, and the rows that break it. For other text, see LEFT, RIGHT and MID.

Do FIND and SEARCH work in every version of Excel?

Yes. Both have been in Excel for a very long time, and both work in Excel for the web.

The lessons below use the same functions. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.