FIND Function

Hi Friends,

In this article I'll explain how to use FIND function in excel, although we do not use this function in basic excel function but it is very useful in Excel VBA or Excel Macro.

So let us learn it first. As the name of this function suggest it is used to find some text or string within a cell. If FIND function is used nested with IF function then it can help you a lot.

String means a  text, in programming language we use string instead of text, those who are from computer programming background will understand the logic, for now please note String means Text.

Please note that this function is case sensitive. You must type the proper case while searching a text using FIND function.

Description

The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive.

The FIND function is a built-in function in Excel that is categorized as a String/Text Function The FIND function can be entered as part of a formula in a cell of a worksheet and we will use this property with IF function nested.

Syntax

The syntax for the FIND function in Microsoft Excel is very simple.

=FIND( substring, string, start_position)

Parameters or Arguments

Substring (part of text)

The substring or part of text that you want to find.

String

The string or text to search within.

start_position

Optional. It is the position in string where the search will start. The first position is 1.

Returns

The FIND function returns a numeric value, the first position in the string is 1.

If the FIND function does not find a match, it will return a #VALUE! error.

Examples :

Let's look at some Excel FIND function examples and explore how to use the FIND function as a worksheet function in Microsoft Excel:

FIND Function in Microsoft Excel

Based on the Excel spreadsheet above, the following FIND examples would return:

Case 1 : Finding a particular text/string

            =FIND("Basic",B2,1) in 2nd row or =FIND("blog",B3,1) in 3rd row

FIND Function in Microsoft Excel

Case 2 : Case sensitivity

            I've got an error #VALUE!  -  because I am trying to find 'Fox' in the given text where it is typed as 'fox' and therefore I got an error though the formula is perfect.

            =FIND("Fox",B4,1)

FIND Function in Microsoft Excel

Case 3 : This example is little bit confusing because you can notice that I am finding for 'w' and the result is 5 i.e., 5th position is the first occurrence of 'w', apparently it is wrong. The output is correct.

Here what I am trying to find is the position of 'w' after the first three 'w's. The formula in the cell I typed is =FIND("w",B5,4) meaning that find me the position of 'w' after the fourth character of the provided string. The below image will clarify it.

FIND Function in Microsoft Excel

           

Hope you have enjoyed this article.

Thank you for reading...