## FIND Function in Excel

Find function in excelis 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.

### Syntax

**Arguments**

**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 us understand the working of FIND in excel by some examples.

#### Example #1

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(B3,A3)

FIND in excel will return 5 as “a” occurs at the 5^{th} position in “Leopard.”

Instead of cell references, you may directly enter the characters as shown in the following the formula for FIND in excel:

FIND(“a”, “Leopard”)

It will also return 5.

#### Example #2

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” occurs 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.**ISNUMBERISNUMBERISNUMBER function in excel is an information function that checks if the referred cell value is numeric or non-numeric. Its output is a boolean value (“True,” if the “value” parameter is numeric or “False” if the “value” parameter is non-numeric).read more (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 ExcelSUMPRODUCT In ExcelThe SUMPRODUCT function in excel is used to multiply the array or range and provide the sum of a product. It is to calculate a weighted average. SUMPRODUCT formula is used to calculate the sum of corresponding numbers product in one or more ranges/arrays.read more (– ( 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. Similar functionality can be seen in the COUNTIF function also. However, this syntax is different from the 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.

#### Example #3

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

#### Example #4

Suppose you have some picture captions, as shown below.

From there, you want to extract only the 1^{st} 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 1^{st}space occurring in the string #Wedding in Jaipur.

**MID(C3, FIND(“#”,C3), FIND(” “, … ))**– will cut C3 from FIND(“#”,C3) to the 1^{st}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 1^{st}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 referencesCell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more. - The FIND function returns the position of the 1
^{st}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 1^{st}character of the 1^{st}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

### Recommended Articles

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 –

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion

## Leave a Reply