Excel SEARCH and FIND Functions: Step-by-Step Guide (2024)
The FIND function of Excel finds and returns the position of a character in a given text string.
Interestingly, the SEARCH function does the same job too. Then why do we need two different functions to do the same job 🤔
To your surprise, both these functions perform the same job but have different features.
What are those? And how can you use these functions?
To learn that (and much more about these functions), dive straight into the guide below. And do not forget to download our sample workbook here as you glide in.
Table of Contents
How to use the FIND function
The FIND function is a straightforward function with three basic arguments. It searches for a character in a given text string and returns its relative position.
How? Check it out here 👇
Let’s find the relative position of the character ‘p’ in the word company. For that:
- Write the find_text argument of the FIND function as follows:
= FIND (“p”,
As the first argument of the FIND function, we write the character to be found ✍ Note that the character (or substring) must be enclosed in double quotation marks “”.
- Type in the text string from where the character’s position is to be found as the [within_text] argument.
= FIND (“p”, “company”)
We have typed in the word “company” enclosed in double quotation marks.
The third argument of the FIND function is the [start_num] argument.
It tells Excel to start counting from a particular character. This is an optional argument, and you may choose to omit it.
Kasper Langmann, Microsoft Office Specialist- Hit “Enter”, and you are good to go!
Excel returns the relative position of “p” from the text string “company” as 4. This is because “p” is the fourth letter of the word “company” 4️⃣
That’s how easy it is to use the FIND function in Microsoft Excel.
Now let’s see a variation to it.
- Write the FIND function to find “c” from the text string “cycle”.
= FIND (“c”, “cycle”
- Specify the start_num argument as 2.
= FIND (“c”, “cycle”, 2)
The result is 3. But we can see the character “c” comes first in the text string “cycle”. Then why does the FIND function return 3?
As we set the [start_num] argument to 2, Excel starts counting the characters from the second substring.
Start searching for the substring “c” in the word “cycle” but skip out the first two letters. The next “c” comes in the third position 🚴♀️
C Y C L E
And so, Excel returns 3.
When the [start_num] argument is omitted, Excel by default sets it to 1. And hence, the character count starts from the first character.
Pro Tip!
Excel will return a #VALUE error if you set the [start_num] argument to:
- Zero (0)
- A negative number
- Or to a number that is greater than the number of words in the [within_text] argument.
For example, you set the [start_num] argument for the function above to 6, whereas the number of characters in the word “cycle” is only 5 🚩
How to use the SEARCH function
Now comes the turn of the SEARCH function.
The Excel SEARCH function is very similar to the FIND function. However, unlike the FIND function, it is case-insensitive.
Let’s see a quick example of the SEARCH function here 👀
We are on a mission to find the position of the character “b” from the text string “My Ball”.
- Write the SEARCH function as shown below.
= SEARCH ( “b”
As the first argument, we have written the character to be found. And it must be enclosed in quotation marks.
- Next, write in the text string from where the character’s position is to be found.
= SEARCH (“b”, “My Ball”)
Close off the function right here. Or you may define the [start_num] argument if you don’t want the character count to begin from the first character.
- Hit “Enter”, and there you have your results.
The result is 4 again, and we know that’s correct ✅
By the way, this reveals that Excel counts in the space characters too. The substring “b” comes in fourth place after M, Y, and a space character.
Pro Tip!
Did you note that the SEARCH function is case insensitive?
The character “b” in our function above is in lowercase. Whereas the “b” in “My Ball” is in upper case ⚽
But that makes no difference to the SEARCH function. It was still able to find the relative position of the character “b” in “My Ball”.
SEARCH vs. FIND: The differences
We have primarily seen how to apply the FIND and SEARCH functions in Excel.
But the question still stands unanswered – why did Excel introduce two functions to perform the same job?
That’s because both these functions are quite different in the following areas:
Case Sensitivity
Let’s quickly find the position of “Money” in the text string “Time is money” using the SEARCH function.
Write the SEARCH function as follows:
= SEARCH ( “Money”, “Time is money”)
Note that the “M” for Money in our first argument is in upper case. Whereas the “m” of money in “Time is money” is a lowercase character🔍
Kasper Langmann, Microsoft Office SpecialistThe SEARCH function returns 9. And that’s right – M of money is the 9th character of the text string “Time is Money”.
Now let’s do the same with the FIND function.
Write the FIND function as follows:
= FIND (“Money”, “Time is money”)
That’s bizarre! The FIND function returns the #VALUE Error.
That’s because the FIND function looks for money with a capital M in the given text string. And returns the #VALUE error upon failing to find any.
This proves that the SEARCH function is case-insensitive, whereas the FIND function is case-sensitive.
Pro Tip!
Here’s something for you to observe.
What happens when you specify more than a single character as the find_text argument? Like “Money” in the example above 💸
Both the SEARCH and the FIND functions return the position of the first character of the find_text argument. (which is “M” in this case)
Wild Card Characters
Another big difference between the FIND and the SEARCH functions!
The SEARCH function accepts wild card characters. However, the FIND function doesn’t.
- Use a wildcard character with the SEARCH function as below.
= SEARCH (“Excel?????”, B2)
The word Excel is followed by five question marks (wildcard characters)❓ These question marks represent any possible five characters after the word Excel.
Kasper Langmann, Microsoft Office SpecialistThe within_text is specified in Cell B2, so we have created a reference to it.
- Hit “Enter”, and there you go!
Excel gives the result 7. That’s because the “E” comes at the seventh position in the text string “Great Excel 2019”.
The SEARCH function applied the wildcard characters to substitute a space character, 2, 0, 1, and 9.
It’s time we do the same using the FIND function.
- Use the same wildcard characters with the FIND function as below.
= FIND (“Excel?????”, B3)
- Hit Enter.
Excel returns a #VALUE Error. That’s because the FIND function treats each question mark as an actual question mark.
And fails to find them in the text string “Great Excel 2019” 😵
That’s it – Now what?
Woohoo! Up till now, we have seen practical examples of how the SEARCH and FIND function can be used in Excel.
We also saw the features that differentiate both these functions through practical examples. And let’s not miss out on the little tips we have been discussing throughout the guide.
Enjoyed learning about these easy yet useful functions of Excel? If yes, you’d be surprised to know that these functions don’t even make a percent of the vast function library of Excel 🗽
There are just so many more smart functions of Excel that you must learn. To mention a few, the VLOOKUP, SUMIF, and IF functions of Excel.
Here is the link to my 30-minute free email course that will teach you these (and many more) functions of Excel.
Other resources
That’s all about finding the relative position of a substring from a text string in Excel.
But what if you want to extract a substring out of a cell in Excel? Three functions will help you extract substrings from different parts of a cell.
Learn How to Extract Substrings in Excel with LEFT, RIGHT, and MID Functions.