The Excel FIND Function

Related Function:
SEARCH function

Basic Description

The Excel FIND function returns the position of a specified character or sub-string within a supplied text string.

The function is case-sensitive. If you want to perform a non-case-sensitive search, use the Excel Search function instead.

The syntax of the Find function is :

FIND( find_text, within_text, [start_num] )

Where the function arguments are:


find_text - The character or text string that you wish to find.
within_text - The text string that is to be searched.
[start_num] -

An optional argument that specifies the position of the character from which the search should begin.

If omitted, this takes on the default value of 1 (i.e. begin the search at the start of the within_text string)

If the supplied find_text is found, the Find function returns a number, representing the position of the find_text in the within_text. If the supplied find_text is not found, the Excel #VALUE! error is returned.


Find Function Examples

The following spreadsheet shows examples of the Excel Find function used to find various characters in the text string "Original Text".

 Formulas:

  A B
1 Original Text =FIND( "T", A1 )
2 Original Text =FIND( "t", A2 )
3 Original Text =FIND( "i", A3 )
4 Original Text =FIND( "i", A4, 4 )

 Results:

  A B
1 Original Text 10
2 Original Text 13
3 Original Text 3
4 Original Text 5

Note that, in the above examples:


Example 2 - Use of the Find Function to Show Cells Containing Specific Text

 Formulas:

  A B C
1 Employee Skills  
2 Jane SMITH Shorthand, Typing, Visual Basic, Excel =FIND( "Typing", B2 )
3 Julie BROWN Presentation Skills, Management Training =FIND( "Typing", B3 )
4 Dan TAYLOR Microsoft Office, Typing =FIND( "Typing", B4 )
5 James JONES Typing, HTML Programming =FIND( "Typing", B5 )

 Results:

  A B C
1 Employee Skills  
2 Jane SMITH Shorthand, Typing, Visual Basic, Excel 12
3 Julie BROWN Presentation Skills, Management Training #VALUE!
4 Dan TAYLOR Microsoft Office, Typing 19
5 James JONES Typing, HTML Programming 1

The above example uses the Excel Find function to highlight cells containing a specific text string.

The example spreadsheet lists employees of a company, alongside a list of skills. The Find function is used in columnn C, to pick out the members of staff who have the skill 'Typing'.

When the string "Typing" is found, the function returns the position of the start of this string within the searched cell. If the string "Typing" is not found, the function returns the #VALUE! error.

Tidying Up Your Result

You might want to tidy up the results of the Find function in the example above. This can be done using the Excel If and Iserror functions. For example, the formula in cell C2 could be written as

=IF( ISERROR( FIND( "Typing", B2 ) ), 0, 1 )

This would return the value 1 if the text "Typing" was found in cell B2 and 0 otherwise.


Further information and examples of the Excel Find function can be found on the Microsoft Office website.


Excel Find Function Error

If you get an error from the Excel Find function this is likely to be the #VALUE! error:

Common Error

#VALUE! -

Occurs if either:

- the supplied find_text is not found in the supplied within_text string
or
- the supplied [start_num] is less than zero or is greater than the length of the supplied within_text string.
Return to the Excel Text Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net