FIND Function in Excel

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.

Syntax

FIND Formula in Excel

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.

You can download this FIND Function Excel Template here – FIND Function Excel Template

Example #1

Suppose you want to find the location of “a” in “Leopard.”

FIND Function Example 1

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 Function Example 1-1

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

FIND Function Example 1-2

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

FIND(“a”, “Leopard”)

FIND Function Example 1-3

It will also return 5.

Result 1-4

Example #2

Suppose you have a list of text strings in A3:A6, as shown below.

FIND Function Example 2

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

FIND Function Example 2-1

In the above the formula for FIND in excel,

  1. 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.
  2. 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.
  3. — ( ISNUMBER( ….))) – The –(TRUE; TRUE; FALSE; TRUE) will convert the TRUE/FALSE matrix to 1/0 and returns 1;1;0;1.
  4. 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.

FIND Function Example 2-2

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.

Example 3

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

Example 3-1

The formula for FIND in excel is quite similar to the one used in Example 3 with a slight modification.

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

  1. 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
  2. — (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
  3. “–“ converts the TRUE/FALSE to 1/0 and “>0” – makes sure that any value greater than zero is just counted once.
  4. SUMPRODUCT(–((ISNUMBER(….) + ISNUMBER(….) ) > 0) -will return 1,1,1,1,0,0,0,0

The above formula for FIND in excel will return 4.

Example 3-2

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.

Example 4

From there, 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))) ) )

Example 4-1

  1. FIND(“#”,C3) – will find the position of “#” in C3. It will return 9
  1. 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
  1. FIND(” “, (MID(….)) – will find the location of the 1st space occurring in the string #Wedding in Jaipur.
  1. 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

FIND Function Example 4-2

Similarly, you can drag for the rest of the captions and get the result, as shown below.

Example 4-3

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

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
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *