In one of our previous blogs, we had learned how to use the Find and Replace dialog box in excel to find a cell containing a particular string and/or replace its value. Sometimes you may have a need to find the position of a particular text/string in another string in a particular cell. To achieve this, there are two inbuilt functions provided in Microsoft excel. They are the FIND function and the SEARCH function in excel.
In this tutorial, we would unlock how to use the FIND formula in excel along with examples.
Here we go.
When to Use FIND Function in Excel
The FIND excel function enables one to extract the position of a particular string or text in a cell. This function is part and parcel of the text function group.
It is important to note that the excel FIND formula is a case-sensitive formula. This means that it also validates the upper and lower case while finding the position of a string in an excel cell.
If you want to perform case-insensitive search, use the SEARCH function in Excel.
Syntax and Arguments
=FIND(find_text, within_text, [start_num])
The FIND excel formula has three arguments as described below:
- find_text – In this argument, specify the text or string for which you want to find the position.
- within_text – In this argument, specify the text or string within which you want to search for the find_text.
- [start_num] – This is an optional argument. In this argument, specify the position from which the excel should start searching.
Example – FIND Function in Excel
In this section of the blog, we would learn how the FIND function work in Excel with the help of examples.
The below image contains the word ‘Excel Unlocked’ in column A. Let us find the position of E (in upper case) and e (in lower case) using the FIND excel function.
To find the position of ‘E’, use the following formula in cell C2.
=FIND("E",A2)AND
=FIND("e",A2)
Explanation – In the above example, the first formula will return the position of capital ‘E’ in the text in cell A2, which is at the 1st position. Similarly, the second formula will return the position of the small letter ‘e’, which is at the 4th position.
You may also give reference to cells B2 and B3 like this:
From the above example, it is clear that if you keep the [start_num] argument as blank, the FIND formula by default returns the first occurrence of the find_text, and ignores the next one.
Suppose you want to find the position of second ‘e’, use the [start_num] argument, as below:
=FIND(B2,A2,5)
As a result, excel will return the position value as 13 (Excel Unlocked).
Similarly, you can find the position of a text having more than one letter, like this:
In that case, the FIND function will return the position of the first character of find_text (in the above example, is dot).
Do Not Miss These Points
- The excel FIND formula is a case-sensitive formula. Use the SEARCH function for a case-in-sensitive search of string.
- The FIND function will return a #VALUE error,
- if it does not find the find_text in the within_text or
- when the [start_num] value is more than the within_text length.
- The FIND function does not accept wildcard characters (*, ? and ~).
- When find_text contains multiple characters, the FIND function will return the position of the first character (already covered in the example section above).
- When there are multiple occurrences of the find_text, the FIND function will return the position of the first occurrence, by default (unless you specify [start_num]).
- The result of a blank find_text (“”) is 1, as shown below:
Thank you.