FIND and SEARCH practice exercises
Find where a character sits in a text, then use that position with LEFT, MID and LEN to cut text that has no fixed length, like names and email addresses.
How FIND and SEARCH work
FIND and SEARCH tell you where one text sits inside another. Both take the text to look for, the text to look in, and an optional position to start from. The result is a number, the position of the first matching character, or a #VALUE! error if there is no match. FIND tells capitals from small letters and takes no wildcards. SEARCH ignores case and accepts ? and * as wildcards.
=FIND("@",A2) returns 6 for "[email protected]". A position on its own isn't much use, so you feed it to LEFT or MID. =LEFT(A2,FIND("@",A2)-1) returns "mette", the part before the @, because it takes one character fewer than the position of the @.
To check whether a text contains a word, wrap SEARCH in ISNUMBER: =ISNUMBER(SEARCH("pro",A2)) shows TRUE for "ProBook 15" and FALSE for "Basic 15". ISNUMBER turns a position into TRUE and an error into FALSE. Use FIND only when case matters, since =FIND("pro",A2) misses "ProBook". The exercises below cover finding the @, the name before it, the domain after it, checking for a word and counting the words in a cell.
- 1Find the @ in an email with FINDIn B2, use FIND to get the position of the @ in the email address in A2.
- 2Take the name before the @PremiumIn B2, get the part of the email in A2 before the @. Use LEFT, with FIND to work out how many characters to take.
- 3Take the domain after the @PremiumIn B2, get the domain, the part of the email in A2 after the @. Use MID to start one character after the @, and LEN for the number of characters.
- 4Check if a text contains a wordPremiumIn B2, show TRUE if the product name in A2 contains "pro" in any case. Use SEARCH inside ISNUMBER.
- 5Count the words in a cellPremiumIn B2, count the words in A2. There is one more word than there are spaces, and LEN(A2) minus the length without spaces, LEN(SUBSTITUTE(A2," ","")), is the number of spaces.
Functions in this topic
Read the guide
Questions
How do I check if a cell contains certain text in Excel?
Combine IF, ISNUMBER and SEARCH: =IF(ISNUMBER(SEARCH("pro",A2)),"Yes","No"). It finds "pro" anywhere in the cell, in any case. A shorter option is =COUNTIF(A2,"*pro*"), which returns 1 when the cell contains the text and 0 when it doesn't. The stars are wildcards for any characters before and after.
Why does FIND return #VALUE! in Excel?
The text you look for wasn't found. Common causes are a different capitalization, since FIND is case-sensitive, a typo, or an extra space. A start position larger than the text length gives the same error. Switch to SEARCH if case shouldn't matter, or wrap it: =IFERROR(FIND("@",A2),0) returns 0 when there is no @.
How do I find the second occurrence of a character in Excel?
Use the start position argument to begin just after the first match. =FIND(" ",A2,FIND(" ",A2)+1) returns the position of the second space. The inner FIND locates the first space, and adding 1 makes the outer FIND start after it. Nest it once more to find the third space.