

The two formulas FIND and SEARCH in Excel are very similar. They search through a cell or some text for a keyword or character. Once found, they return the number of characters, at which the keyword starts. Let’s learn how to use them and explore the differences of the two formulas.
Contents
The formula returns the number of the character, at which your search term starts. If your text can’t be found, it’ll return a “#VALUE” error.
The FIND formula has at least two arguments: The SEARCH TERM as well as the “WITHIN TEXT”. The third part is optional.
The structure of the FIND formula is quite simple (the numbers are corresponding to the image on the right-hand side):
If you just want to know, if your text can be found within another cell, you might want to go with the following formula. B2 contains the text or character you search for and B3 is the cell you search in:
=IFERROR(IF(FIND(B2,B3)>0,”Value found”,””),”Value not found”)
Please note the following comments:
Let’s try some examples.
Example 1: Find “o” in “Hello there”.
The “default” usage of the FIND formula is to return the number of characters, at which a text first occurs within another text. Let’s say you have the text “Hello there” and want to know, at which position you have an “o”. In such case, you write the following formula (example number 1 in the Excel workbook you can download at the end of this article).
=FIND(“o”,”Hello there”)
The return value is 5.
Example 2: Get the position of the first “e” after the third character.
Another example: You want to know the position of “e” after the third character (example number 2 in the example workbook).
=FIND(“e”,”Hello there”,3)
The return value is 9.
Example 3: Get the position of the second “e” in “Hello there”.
Now you want to know the position of the second occurrence of “e” in “Hello there”.
=FIND(“e”;”Hello there”;FIND(“e”;”Hello there”)+1)
This formula works as follows. The second FIND formula returns the position of the first occurrence of “e” in “Hello there” which is 2. This is used as the START NUMBER (+ 1 because you want to start counting after the first “e”) for the first FIND formula.
Example 4: Get the position of the last occurrence of “e” in “Hello there”
Finding the last occurrence of a string within a text is a little bit more complicated. You use SUBSTITUTE and LEN. The complete formula is like this:
=FIND(“tempreplacetext”;SUBSTITUTE(“Hello there”;”e”;”tempreplacetext”;SUM(LEN(“Hello there”)-LEN(SUBSTITUTE(“Hello there”;”e”;””)))/LEN(“e”)))
In order to understand the formula better, let’s start in the middle with the part SUM(LEN(“Hello there”)-LEN(SUBSTITUTE(“Hello there”;”e”;””)))/LEN(“e”)))
This part of the formula determines the number of “e” in “Hello there”. It replaces “e” in “Hello there” and the difference in the two versions (with and without e – “Hello there” and “Hllo thr”) is the number of “e”s.
The whole part SUBSTITUTE(“Hello there”;”e”;”tempreplacetext”;SUM(LEN(“Hello there”)-LEN(SUBSTITUTE(“Hello there”;”e”;””)))/LEN(“e”))) replaces the last “e” by “tempreplacetext”. Now you just put this into the FIND formula and get the position of “tempreplacetext”.
If you want to use this formula, replace all “e”s by your cell reference for the SEARCH TEXT and all “Hello there” by the cell reference to your WITHIN TEXT.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
Example 5: Return the text between the brackets of “Hello (you) there”.
A last example for the FIND formula: You want to know what is written between two brackets. In such case, you can also use the FIND formulas in combination with the MID formula.
=MID(“Hello (you) there”,FIND(“(“,”Hello (you) there”)+1,FIND(“)”,”Hello (you) there”)-FIND(“(“,”Hello (you) there”)-1)
The MID formula returns some part of text from a (longer) text. It has three arguments:
In our example above, the first FIND formula provides the character you want to start at. In our case that’s the first opening bracket (+1 because you want to start one character later). The second and third FIND formulas determine the length by
Like the FIND formula in Excel, SEARCH also returns the position of a text within another text.
The SEARCH formula has the same arguments as the FIND formula.
The SEARCH formula is very similar to the FIND formula. It has exactly the same arguments and works the same way.
Because of the, the structure is as follows:
Yes, you are right – these arguments are exactly the same like in the FIND formula. But there is a major difference: SEARCH does not regard lower and upper case: It is not case sensitive. FIND on the other hand is case-sensitive and regards upper and lower cases.
The examples work exactly the same way like for the FIND formula. You only have to replace “FIND” by “SEARCH”. Because of that, we just show the summary below. If you want to know more, either scroll up to the “FIND”-examples or download the example workbook below.
Example no. | Description | Formula |
6 | Return the position of “o” in “Hello there”. | =SEARCH(“o”,”Hello there”) |
7 | Return the position of “e” in “Hello there” after the third character. | =SEARCH(“e”,”Hello there”,3) |
8 | Return the position of the second occurrence of “e” in “Hello there”. | =SEARCH(“e”,”Hello there”,SEARCH(“e”,”Hello there”)+1) |
9 | Return the position of the last occurrence of “e” in “Hello there”. | =SEARCH(“tempreplacetext”,SUBSTITUTE(“Hello there”,”e”,”tempreplacetext”,SUM(LEN(“Hello there”)-LEN(SUBSTITUTE(“Hello there”,”e”,””)))/LEN(“e”))) |
10 | Return the text within brackets of “Hello (you) there”. | =MID(“Hello (you) there”,SEARCH(“(“,”Hello (you) there”)+1,SEARCH(“)”,”Hello (you) there”)-SEARCH(“(“,”Hello (you) there”)-1) |
The error message “#VALUE!” comes up most often for the FIND and SEARCH formulas. If you receive a “#VALUE!” error, please check the following possibilities.
The first and major difference between FIND and SEARCH:
FIND is case-sensitive. SEARCH is not case-sensitive.
That means, FIND regards capital and small letter whereas SEARCH doesn’t.
How to remember which is which? I use the mnemonic:
I admit, it’s not the best mnemonic. Please let me know, if you have a better one!
There is also a second difference between FIND and SEARCH: You can use SEARCH with wildcard criteria within the SEARCH TERM.
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
Maybe you have noticed that there is also a slightly different version of the two formulas in Excel. If you add a “B” to the formulas, you can still use them the same way. So what is the difference?
FINDB and SEARCHB provide support for more complex languages. The definition by Microsoft is:
SEARCHB counts 2 bytes per character only when a DBCS language is set as the default language. Otherwise SEARCHB behaves the same as SEARCH, counting 1 byte per character.
According to Wikipedia DBCS stands mainly for the Asian languages Chinese, Japanese and Korean.
Please feel free to download all the examples shown above in this Excel file.
Click here and the download starts immediately.
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.