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:

Excel Tips - Find Search 1.png

To get the Last Name:

'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

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.

Picture time:

Excel Tips - Find Search 2.png

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_textExcel Tips - Find Search 3.png

Tip: why not using FIND directly for finding “?” or “*”…?


The following demonstrates the difference of case sensitivity:Exel Tips - Find Search 4.png

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.

This entry was posted in Formula and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s