Wednesday 15 March 2017

Excel - Microsoft Excel Find Function

Description

The Excel FIND function returns the position of a specified character or 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:


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". 
The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right.


Note that, in the above examples:



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


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

The example spreadsheet lists members of staff for 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. 

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

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

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



Excel Find Function Error

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


















No comments:

Post a Comment