How to Use the FIND Formula in Excel
The FIND formula in Excel is used to find any text from a selected range of texts. The FIND formula will return the relative position of where the text being searched is located in the cell it is being searched in.
- Find_text: The text which we are attempting to find.
- Within_text: The text which we want to search.
- Start_num: The numerical position of the text in which we are beginning to start the search. If omitted start_num=1.
In this example, we are going to attempt to find the text in the “Find_text” column from the “Within_text” column.
This means that the “are” word is located at the fifth character of the cell D28 containing the word “How are you”
In this example, we are using character 8 as the starting point of the analysis. Character 8 is where the word “in” begins and therefore it does not alter the result. The FIND formula will begin searching at the 8th character and it returns the result that it found it at the 8th character.
Next, we perform the search of the word “in” but this time we start the search at the 9th character n the “within_text” column. In this case, the search does not pick up the “in” since it has begun the search at the letter n. It does locate the second word “in” within the sentence and returns the result 17 since the second word “in” begins at character 17 in the sentence.
Note that the FIND formula is case sensitive. For example, let’s assume we want to perform a lookup of the letter e in the word example and the word example has a capital E for the first letter.
Text lookup Lookup value
In this solution, we can see that the FIND formula returns the 7th character for e which is the last letter of the word because our search was a lower case.
So if we were to perform a lookup of the lowercase letter E with an example, it is going to find it as the 7th character which is the last letter of the word example.
If we were to change the letter e to an uppercase, it returns a result of 1 because it finds the capital letter E as the first character of the word ‘Example’. Hence, it just goes to show that the find formula is case-sensitive.
I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!