This Excel tutorial explains how to use the Excel FIND function with syntax and examples.
string is the string to search within.
start_position is optional. It is the position in string where the search will start. The first position is 1.
Based on the spreadsheet above, the following Excel FIND examples would return:
Objective: If A1 contains "Super", then I want A3=A2. Otherwise, I want A3=0. I'm unable to use the FIND function because if cell A1 does not contain "Super", the FIND function returns the #VALUE! error which does not let me sum column A.
Answer: To make sure that do not return any #VALUE! errors when using the FIND function, you need to also use the ISERROR function in your formula.
Let's look at an example.
Based on the spreadsheet above, the following Excel FIND examples would return:
Based on the spreadsheet above, the following Excel FIND examples would return:
Let's just quickly explain how this formula works. If cell A1 contains "Super", the FIND function will return the numerical position of the value of "Super". Thus, an error will not result (ie. the IsError function evaluates to FALSE) and the formula will return A2.
If cell A1 does NOT contain "Super", the FIND function will return the #VALUE! error causing the IsError function to evaluate to TRUE and return 0.
Description
The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive.Syntax
The syntax for the Microsoft Excel FIND function is:FIND( substring, string, [start_position] )
Parameters or Arguments
substring is the substring that you want to find.string is the string to search within.
start_position is optional. It is the position in string where the search will start. The first position is 1.
Note
- If the FIND function does not find a match, it will return a #VALUE! error.
Applies To
The FIND function can be used in the following versions of Microsoft Excel:- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Excel Function
The FIND function can be used in Microsoft Excel as the following type of function:- Worksheet function (WS)
Example (as Worksheet Function)
Let's look at some Excel FIND function examples and explore how to use the FIND function as a worksheet function in Microsoft Excel:Based on the spreadsheet above, the following Excel FIND examples would return:
=FIND("Alpha", A1, 1)
Result: 1
=FIND("alpha", A1, 1)
Result: #VALUE!
=FIND("alpha", A2, 1)
Result: return 1
=FIND("the", A3, 3)
Result: 11
=FIND("ph", "Alphabet", 2)
Result: 3
Frequently Asked Questions
Question: In Microsoft Excel, I have the value "Supermarket" in cell A1 and 100 in cell A2.Objective: If A1 contains "Super", then I want A3=A2. Otherwise, I want A3=0. I'm unable to use the FIND function because if cell A1 does not contain "Super", the FIND function returns the #VALUE! error which does not let me sum column A.
Answer: To make sure that do not return any #VALUE! errors when using the FIND function, you need to also use the ISERROR function in your formula.
Let's look at an example.
Based on the spreadsheet above, the following Excel FIND examples would return:
=IF(ISERROR(FIND("Super",A1,1))=TRUE,0,A2)
Result: 100
In this case, cell A1 does contain the value "Super", so the formula returns the value found in cell A2 which is 100.Based on the spreadsheet above, the following Excel FIND examples would return:
=IF(ISERROR(FIND("Super",A1,1))=TRUE,0,A2)
Result: 0
In this example, cell A1 does NOT contain the value "Super", so the formula returns 0.Let's just quickly explain how this formula works. If cell A1 contains "Super", the FIND function will return the numerical position of the value of "Super". Thus, an error will not result (ie. the IsError function evaluates to FALSE) and the formula will return A2.
If cell A1 does NOT contain "Super", the FIND function will return the #VALUE! error causing the IsError function to evaluate to TRUE and return 0.
