The Excel FIND function returns the position of a character or sub-string within a supplied text string. Function returns output only in number form as the starting position of a character.
=FIND( find_text, within_text, [start_num] )
- find_text – The specific character or sub-string to find.
- within_text – The text string within “find_text” argument to be searched
- [start_num] – An optional argument that specifies the starting position of the searched text string within text argument. When skipped, it default starts searching from the first character “within_text”.
Useful Additional Notes:
- Function is case-sensitive and does not support wildcard characters.
- It returns the position of the first instance of “find_text” in “within_text” argument as default.
- When more than one character is supplied in “find_text” argument, function always returns the position of the first character of the sub-string.
- It returns the position (output) as 1 of first character when empty text “” is supplied in “find_text” argument as character.
- This function will return #VALUE! error in following cases:
- When first argument “find_text” does not exist in “within_text argument
- When either “start_num” is supplied as less than equal to 0 (zero) or when “Start_num” contains more character than the actual text string
- Use SEARCH function if case-insensitive search to be performed or need to use wildcard characters.
Examples – FIND Function:
Example 1: Returns the position of first instance of small letter “e”
In the first example, the position of small letter “e” is need to be searched. Since FIND function is case-sensitive, “e” is supplied in lowercase in “find_text” argument of FIND function and asked function to search in cell C5 that contains Dummy Text as “Microsoft Excel is Excellent”.
The FIND function looks for first instance of small case “e” and stops the search at 14th position and returns 14 as the output in result column.
Example 2: Find Nth occurrence of a space in a text string
Method 1: Using FIND function
To find Nth occurrence of any given character or sub-string within a text using FIND, we need to nest FIND function inside to get the same. In this case, third argument of FIND function plays an important role.
In the above method, FIND is used two times. Let’s explore the formula mentioned as follow:
=FIND( ” “, C14, FIND(” “,C14) +1 )
The first two arguments help main FIND function to locate the space in cell C14. The third argument contains the FIND function which has the same argument but added with 1 and it helps to start searching from 6th character onward. When main FIND looks for space (” “) from 6th character onward, it finds next space located at 11th position.
Method 2: Using FIND with SUBSTITUTE Function
In this situation, let’s find 3rd occurrence of space in a given text string but using different method.
To find the same, we use FIND with SUBSTITUE Function. The SUBSTITUTE function helps us in replacing one or more instances of old text with new text in a supplied text string.
This combination is the simpler way of finding the Nth occurrence of a specified character in a text string.
=FIND( “#”, SUBSTITUTE( C8, ” “, “#”, 3 ) )
In the above formula, the SUBSTITUTE function replaces the third occurrence of space (” “) with hash ( # ) for cell C8 and then FIND function finds the hash ( # ) sign in new text string returned by SUBSTITUTE function and finds at 19th position and returns the same in cell.
Note: The symbol or character supplied in first argument “find_text” of FIND function, should not exist anywhere in original text string. You can also use CHAR function to insert non-printable character in place of inserting the character manually.
Example 3: Get first name from name column and return
In a situation where we have to extract leftmost word or first name, we can combine LEFT with FIND to extract the same.
LEFT needs number of characters to be extracted from the starting of the supplied text. Let’s explore the formula we used in this case:
=LEFT( C5, FIND( ” “, C5 ) -1 )
In the above formula, FIND function returns the position of the first occurrence of space. For first record, it locates the first space at 6th position and then 1 is subtracted from result of FIND function to exclude the space in the result. Finally LEFT is instructed to extract 5 characters from beginning of C5 and returns as “James” in cell E5.
Use SEARCH instead of FIND function if CASES does not make any difference. Likewise, FIND can be used with other Excel built in functions e.g. MID, RIGHT, LEN, SUBSTITUTE etc.
We hope the given examples in this tutorial would be helpful to understand the FIND function and its uses in different situation. We will come back with more examples of FIND function with other functions and features of Excel. Meanwhile if you like to download the “Sample File of FIND Function”, click on the link given below. You can also watch the video of FIND function with related examples given below.
LEFT | RIGHT | MID | SUBSTITUTE | SEARCH