SEARCH and FIND in Excel: how to use it
Both functions give the position of a text inside another text. SEARCH ignores upper and lower case and knows wildcards, FIND doesn't.
Try the first exercises for freeIn the lesson SEARCH and FIND
What do SEARCH and FIND do?
SEARCH looks for a character or a piece of text in a cell and gives back the position where it starts: at the first character that's 1, at the fifth it's 5. FIND does the same, but pays attention to capitals and doesn't understand wildcards.
A position on its own doesn't say much. You use it as a building block: with LEFT, RIGHT or MID you then cut a piece out of a text that isn't always the same length, such as the name before the @ in an email address.
When do you use SEARCH and FIND?
- Where the @ is in an email address: =SEARCH("@",A2).
- Everything before the @: =LEFT(A2,SEARCH("@",A2)-1).
- Checking whether a word is in a cell: =ISNUMBER(SEARCH("quick",A2)).
- Searching with attention to capitals, such as a capital X in a code: =FIND("X",A2).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The position of a text, without paying attention to capitals. Understands wildcards.
Filled in: =SEARCH("@",A1)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SEARCH | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SEARCH |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
find_text | The character or text you're looking for, in quotation marks, such as "@". | "@" |
within_text | The cell you're searching in. | A1 |
[start_num] | Optional: the character from which Excel starts searching. By default, that's the first one. | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
SEARCH and FIND step by step
C1 contains =SEARCH("@",A1). The @ is in the fifth position. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type SEARCH with a bracket.
=SEARCH( - Type what you're looking for in quotation marks: "@". Type a comma.
=SEARCH("@", - Click the cell you're searching in, A1.
=SEARCH("@",A1 - Close the bracket and press Enter: the @ is in the fifth position.
=SEARCH("@",A1)
More examples
Everything before the @
C1 contains =LEFT(A2,SEARCH("@",A2)-1). SEARCH gives the position of the @, here 14. LEFT takes all the characters before it: 13.
Searching with a wildcard
C1 contains =SEARCH("d?livery",A2). The question mark stands for one character. SEARCH understands wildcards, FIND doesn't.
Capitals with FIND
C1 contains =FIND("X",A2). FIND pays attention to capitals: the small x at the start doesn't count, and the capital X is in position 6. SEARCH would give 1.
Practise this in the lesson SEARCH and FIND. The first exercises are free.
Frequently asked questions
How do I check whether a cell contains a word?
With =ISNUMBER(SEARCH("word",A1)).
Read next
LEFT, RIGHT and MIDTRIMLENUPPER, LOWER and PROPERJoining text