The FIND function returns the starting position of one text string within another text string. It is case-sensitive.
Explanation of the FIND function
The syntax of the function is: FIND(find_text, within_text, [start_num]). If we translate this syntax into plain English, it reads: What do you want to find? Where do you want to find it? What position do you want to begin from?
The FIND function takes three arguments two of which are required and one optional:
- The find_text argument is required and stands for the text string for which we want to search its starting position.
- The within_text argument is required and stands for the text string within which we want to search.
- The start_num argument is optional and stands for the number of the start position for the search. If it is not provided it defaults to the value of 1.
The FIND function returns the position (as a numeric value) of one text string that is within another. If there is more than one instance of the search string, the FIND function returns only the first instance.
FIND function does not support the use of wildcard characters. When the find-text argument is an empty string, the function returns 1.
Sometimes the FIND function doesn’t work as expected and returns #VALUE! error. In this tutorial, we explore 5 causes for this problem and possible solutions.
Cause 1: The “within_text” string does not contain the “find_text” string
We will use the following dataset to show that the FIND function does not work if the text string we are searching for is not part of the text string we are searching from:
- Select cell C2 and type in the formula =FIND(B2,A2) as follows:
- Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:
The FIND function returned #VALUE! error for the first 3 records because the “find_text” string was not found in the “within_text” string.
The FIND function returned the correct values for the last 2 records because the “find_text” string was found in the “within_text” string.
Solution
Replace the “find_text” strings with text strings that are found in the “within_text” strings.
Cause 2: Case sensitivity of the FIND function
The FIND function is case-sensitive. This means that if the “find_text” string does not match exactly the corresponding string in the “within_text” string, the function returns #VALUE! error or a wrong unexpected value.
We will use the following dataset to show this:
In the dataset, the FIND function has returned #VALUE! error for 4 records and a wrong unexpected value for the second record. This is because of the case sensitivity of the FIND function.
Although the text strings in column B are contained in the text strings in column A, the FIND function could not locate them because they do not match exactly the corresponding strings.
Solutions
- Replace the “find_text” strings in column B with the ones that match exactly the corresponding strings in the “within_text” strings in column A.
- If we don’t want to match the case, we can use the SEARCH function instead of the FIND function. These two functions are very similar and work the same way except for the fact the FIND function is case-sensitive and the SEARCH function is not as the following dataset shows:
Cause 3: The start_num is greater than the length of the “within_text” string
When we use the FIND function, the start_num argument must not be greater than the length of the “within_text” string otherwise the #VALUE! error occurs.
We use the following dataset to explain this:
- Select cell C2 and type in the formula =FIND(B2,A2,10) as follows:
- Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:
The FIND function returns #VALUE! error values because the start_num argument has a value of 10 which is greater than the length of all the “within_text” strings.
Solution
We can omit the start_num argument so that Excel uses the default value of 1 or pass to the function a number that is equal to or less than the length of the “within_text” argument in column A.
Cause 4: The start_num argument is set to zero
The start_num argument is optional and if it is omitted, the default value is assumed to be 1. If we do not omit the argument but set it to zero (0), the FIND function returns #VALUE! error.
We use the following dataset to show how this error occurs:
- Select cell C2 and type in the formula =FIND(B2,A2,0) as follows:
- Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:
Cause 5: Mistyping cell reference in the formula
If we type a cell reference directly into a cell we can make mistakes and type in the wrong reference. This makes the FIND function return the #VALUE error or a wrong value. Like in the example below:
The wrong cell reference of E2 was typed into the formula instead of the correct cell reference of B2. The FIND function returned the #VALUE! error.
Solution
We should never type a cell reference into the formula directly because we could misread and mistype it. We should use the mouse to click on the cell range we want to use. Excel automatically inserts the cell reference into the formula.
Conclusion
The FIND function is case-sensitive and is used to return the starting position of one text string within another text string.
Sometimes this function does not work as expected and either returns wrong values or the #VALUE! error.
In this tutorial we explored 4 possible causes of this problem: the text string looked for is not found within the string searched from, the text string looked for does not match exactly the corresponding text string within the string searched from, the value for the start number is greater than the length of the string searched from, and the start number argument is set to zero.
We also explored solutions to the causes.