FIND Function in Excel
Find function in excel is used to find the location of a character or a substring in a text string. In other words it is used to find the occurrence of a text in another text, as it gives us the position of the target text so the output returned by this function is an integer and this function is a text function in excel which is an inbuilt function which uses three arguments in it.
- find_text: The text to find.
- within_text: The text string to be searched within
- start_num: Optional. It specifies from which character the search shall begin. The default is one.
How to use FIND Function in Excel? (with Examples)
Let understand the working of FIND in excel by some examples.
Suppose you want to find the location of “a” in “Leopard”.
If “Leopard” is given in A3 and “a” is given in B3 as shown above, the formula for FIND in excel will be:
FIND in excel will return 5 as “a” occurs at the 5th position in “Leopard”.
Instead of cell references, you may directly enter the characters as shown in the following the formula for FIND in excel:
It will also return 5.
Suppose you have a list of text strings in A3:A6 as shown below.
In this list, you want to identify the number of items the character “i” is occurring in the list. You may use the following formula for FIND in excel to identify this:
=SUMPRODUCT( — (ISNUMBER (FIND (“i”, A3:A6))))
In the above the formula for FIND in excel,
- FIND(“i”, A3:A6) -will find the position in cells which contain the find_text “i” and gives an error when it cannot find the position.
- ISNUMBER (FIND(..)) – will return TRUE when the value is numeric and FALSE when not. So, when the FIND function in excel finds “i”, it will return TRUE and when it can not, it returns FALSE. So, it forms a matrix: TRUE; TRUE; FALSE; TRUE.
- — ( ISNUMBER( ….))) – The –(TRUE; TRUE; FALSE; TRUE) will convert the TRUE/FALSE matrix to 1/0 and returns 1;1;0;1.
- SUMPRODUCT in Excel (– ( ISNUMBER( ….))) – The SUMPRODUCT (1;1;0;1) will finally sum and return 3.
It may be noted here that the FIND function considers only the occurrence of “i” in the cells (presence or absence). If it occurs more than once, then also it is counted as one. For example, if Onion becomes “Oniion”, then also it will return 3. A similar functionality can be seen in the COUNTIF function also. However, this syntax is different from COUNTIF function as it is case-sensitive while COUNTIF is not.
In the above example, you learned how to extract the number of cells in which a substring is occurring in a range of cells. Now, let us see how to find the number of cells containing either of two different substrings (substring A or substring B) in a range of cells.
Suppose you have a list of names as shown below.
In this list, you want to find the (number of) names in which “ansh” or “anka” occurs. You can do so using the following formula for FIND in excel:
=SUMPRODUCT(– (( ISNUMBER( FIND(“ansh”, A3:A10)) + ISNUMBER( FIND(“anka”, A3:A10))) >0))
The formula for FIND in excel is quite similar to the one used in Example 3 with a slight modification.
- ISNUMBER( FIND(“ansh”, A3:A10))
will return a matrix of TRUE/FALSE depending on the occurrence of “ansh” in A3:A10 and will return: FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE
- ISNUMBER( FIND(“anka”, A3:A10)) – will return a matrix of TRUE/FALSE depending on the occurrence of “anka” in A3:A10 and will return: TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE
- — (ISNUMBER( FIND(“ansh”,..)) + ISNUMBER( FIND(“anka”, ..)) >0 – will add the two boolean matrix and return: TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE è 1;1;1;1;0;0;0;0
- “–“ converts the TRUE/FALSE to 1/0 and “>0” – makes sure that any value greater than zero is just counted once.
- SUMPRODUCT(–((ISNUMBER(….) + ISNUMBER(….) ) > 0) -will return 1,1,1,1,0,0,0,0
The above the formula for FIND in excel will return 4.
Sometimes we need to extract words that start with or contains a specific symbol such as “@” in emails or “//” in URLs etc. Let us see an example to extract such words.
Suppose you have some picture captions as shown below.
From these, you want to extract only the 1st hashtag from each caption. Hashtags start with “#” and end with space. For C3, you can use the below formula for FIND in excel:
=MID(C3, FIND(“#”,C3), FIND(” “, (MID(C3, FIND(“#”,C3), LEN(C3))) ) )
- FIND(“#”,C3) – will find the position of “#” in C3. It will return 9
- MID(C3, FIND(“#”, C3), LEN(C3)) – will extract the text from FIND(“#”, C3) i.e., 9 to the LEN(C3) i.e., the end. Here, it will return #Wedding in Jaipur
- FIND(” “, (MID(….)) – will find the location of the 1st space occurring in the string #Wedding in Jaipur.
- MID(C3, FIND(“#”,C3), FIND(” “, … )) – will cut C3 from FIND(“#”,C3) to the 1st space occurring in the string #Wedding in Jaipur and will return #Wedding
Similarly, you can drag for the rest of the captions and get the result as shown below.
Relevance and Uses
- It is used to extract the number of times a substring or a combination of substrings appears in a range,
- It is used to extract words occurring after a specific character and also to extract the URLs
- It is used to get the first or last name
- It is used to find the nth occurrence of a substring.
- It is used to remove any unwanted text
Things to Remember
- The FIND function in excel searches for the find_text in within_text and returns the position of the 1st occurrence of find_text in within_text.
- The find_text can be a character or a substring. Both find_text and within_text can be text characters or cell references.
- The FIND function returns the position of the 1st occurrence of the find_text in within_text.
- The FIND function is case sensitive and does not allow wildcard characters.
- If the find_text contains more than one character, the position of the 1st character of the 1st match in within_text is returned.
- If find_text is an empty string “”, the FIND function will return one.
- If the Excel FIND function cannot find find_text in within_text, it gives #VALUE! error
- If the start_num is zero, negative or greater than within_text, the FIND function returns #VALUE! error.
FIND Function in Excel Video
This has been a guide to FIND Function in Excel. Here we discuss the FIND Formula in excel and how to use FIND in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –