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:

=LEFT(A2,FIND(",",A2)-1)

So I think it makes sense to talk about FIND and SEARCH.

The syntax

The syntax for FIND and SEARCH is basically the same:

FIND(find_text,within_text,[start_num])
SEARCH(find_text,within_text,[start_num])

where

• 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.

Picture time:

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.