In many situations, we use either FIND or SEARCH together with LEFT, RIGHT, MID to achieve what we need. A typical example is to extract the first name and the last name from a full name where a coma acts as the separator, like the example below:
To get the Last Name:
=MID(A2,FIND(",",A2)+2,99) 'Why 99 in the last argument? You may want to read the previous two post.
To get the First Name:
So I think it makes sense to talk about FIND and SEARCH.
The syntax for FIND and SEARCH is basically the same:
- find_text is the text you want to find. If you wish to input the text directly into the formula, don’t forget to enclose the text with double quotation marks “”
- within_text is where you want to look at. If you wish to input directly into the formula, don’t forget to enclose the text with double quotation marks “”
- [start_num] is the starting position where you want to look for your text; when omitted, Excel assumes 1.
What the functions do?
They both return the position of a specific character or text string that you look for from within a text, starting from the position you specify.
What are the differences?
- FIND is case sensitive while SEARCH is not
- FIND does not accept wildcard (?, *) in find_text while SEARCH does.
As you see in the above example (results of B3 to B6), both functions return the first match from the stating position.
In case you need to find “?” or “*” using SEARCH, concatenate (~) tilde before find_text
Tip: why not using FIND directly for finding “?” or “*”…?
The following demonstrates the difference of case sensitivity:
When I first learned these two functions, I often confused which one is case sensitive and which one is not…
Literally, I believe the two words FIND and SEARCH are different in meaning… But I am not native in English, I find it difficult to tell the difference…
I was thinking why there is
- “Finding Nemo/Dory”, but not “Searching Nemo/Dory”??
- “Lost and Found, but not “Lost and Search”??
- “Find the one”, but not “Search the one”??
Then I come to a “conclusion” that FIND is for something specific, that’s why it is case-sensitive. Make sense? I don’t know.🙂
Do you have any tips in helping us remember the difference between FIND and SEARCH? Please leave a comment.