**Henrik Schiffner**

**Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.**

**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 structure of the FIND formula is quite simple (the numbers are corresponding to the image on the right-hand side):

- SEARCH TERM: The first part of the formula is the text you look for.
- WITHIN TEXT: The cell or text you want to search your “SEARCH TERM” in.
- START NUMBER: This argument is optional. If you don’t want to start searching at the beginning of the text, fill in the number larger than 1, otherwise just type 1 or leave it blank.

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:

**FIND is case-sensitive**. That means, it matters if you use small or capital letters.- You can
**also search for numbers**within another number. For example: =FIND(1,321)This formula returns 3 because the number 1 is the third character of 321. - You
**can’t use wildcard criteria**(“*” or “?”) with the FIND formula. - Please refer to this article if you want to know more about the IF formula and to this article for more information about the IFERROR formula.

Let’s try some examples.

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.

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.

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.

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 how Microsoft Excel performs calculations
- Use the simple and effective step-by-step guide to master each method
- Get to know the impact each method will have on performance

**Learn more or get it on Amazon!**

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:

- The initial, complete text.
- The starting character for the text you want to get.
- The length (in number of characters) of text you want to extract.

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 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:

- The SEARCH TERM is the text or character you search for.
- WITHIN TEXT contains the text you search in.
- The START NUMBER is optional and defines the number of character, after which Excel should start searching.

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.

- Make sure your SEARCH TERM can be found. If it can’t be found, you’ll receive the #VALUE! error.
- The last argument, the START NUMBER, is larger than the number of characters of in your WITHIN TEXT argument.
- You haven’t provided the last argument although you wrote the comma. For instance =SEARCH(“b”,”abc”,).
- Please check, if your SEARCH TERM and WITHIN TEXT have the same format. You can’t search for a letter or text in a number cell.

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:

- FIND finds exactly what you look for.
- Searching for something also searches for roughly what you look for.

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.

- “?” stands for a single character. Example: =SEARCH(“?b”,”abc”)
- “*” matches any sequence of characters. =SEARCH(“*b”,”abc”)
- If you want to search exactly for ? or *, write a tilde in front of them. Example: =SEARCH(“~*”,”ab*c”)

**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?**

**If yes,**why don’t you**subscribe**to our free, monthly Excel newsletter?**If no, please let us know what to improve.**

**Click to print (Opens in new window)****Click to share on WhatsApp (Opens in new window)****Click to share on Facebook (Opens in new window)****Click to share on Twitter (Opens in new window)****Click to share on Tumblr (Opens in new window)****Click to share on LinkedIn (Opens in new window)****Click to share on Reddit (Opens in new window)****Click to share on Pocket (Opens in new window)****Click to share on Pinterest (Opens in new window)****Click to share on Skype (Opens in new window)****Click to share on Telegram (Opens in new window)**

- The best Excel tips, tricks and tutorials.

- 1x per month.

- No spam. Promised.

Save a lot of time with our

If the sign-up form doesn't work, please use this page.

**'Professor Excel Tools': Add more than 60 amazing features to Excel!**

Overview | Features & details

**Professor's Daily Tips for Excel: Get one free Excel tip every day.**

**Professor's Error Helper for Excel: Solve any error in Excel.**

**Screen Sharing Excel files with Zoom & Co Like A Pro: Easy Tricks!****XLOOKUP in Excel: Easily Solve all VLOOKUP Problems!****Convert XLOOKUP to VLOOKUP With These 4 Easy Methods!****Excel Opens Blank Window When Double-Clicking On File? Easy Solution!****Comments to Cells – 3 Methods for Comments and Notes (+Download)**

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.

Click here to go to read our Privacy Policy.

Click here to go to read our Privacy Policy.

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.