In many situations, you need to locate the certain character or text string within another string present in cells. By doing so, you can find the presence of that specified character to extract the required information from other string. For this purpose, Excel has built-in FIND function to perform this activity in many ways with the help of other functions too.
Excel FIND function returns the position of a character or substring in another text string as the number. This function returns #VALUE error where the text string is not found in another text string.
Syntax of FIND function is as follows;
=FIND (find_text, within_text, [start_num])
It has three arguments. First 2 arguments are required and the third one is optional.
find_text: This argument contains character or text string that you need to find in other string. You can supply the character or text string directly or as a cell reference.
within_text: This argument contains text string to be searched within. You can directly supply text string or as a cell reference.
start_num: This argument is optional. It is the position in string where the search will start. The first position is 1.
The FIND function returns a numeric value. The first position in the string is 1. A number represents the location of find_text argument in within_text argument.
Notes to Use FIND function in Excel
There are some points to remember while using FIND function correctly in Excel.
- FIND function is case-sensitive, so it will return the position of the exactly matched text string as the number value.
- If it does not support wildcard characters for partial or case-insensitive search. If you need to locate case-insensitive characters then you must use SEARCH function.
- within_text argument may contain several occurrences of find_text argument, so FIND function will return the first occurrence of find_text argument. For example, if you need to find character “p” in string “Apple” then FIND returns 2, that is the position of first “p” character in text string “Apple”.
- If find_text argument contains several characters in substring then FIND function returns the position of the first character of the substring in within_text argument. For example, if you need to locate substring “le” in string “Apple” then FIND function returns 4, that is a position of first character “l” of substring “le”.
For example, you need to locate the position of various characters or substring within other strings using FIND function in the following example.
In the first case, the start number is optional, so it is set to 1 by default. In this example, you need to find character “A” in word “Apple” and FIND function will return 1 as a position of character “A”.
In the second case, you need to find the first occurrence of character “p” where there are multiple occurrences of find_text character.
In the third case, find_text argument has more than one character to find in within_text argument.
In the fourth case, you need to find the case-sensitive search of substring “the” where the string in within_text argument has lower case and Proper case words, like “the” and “The”.
Finding Nth occurrence of a character in a text String
Suppose you have multiple occurrences of a specified character like the dash (“-“) in a string, and you want to find the position of Nth occurrence of dash character in the following string, then you need to embed another FIND function in main FIND function, like following formulas;
For 2nd occurrence the formula would be like followings;
=FIND("-",A2,FIND("-",A2)+1)
To locate 2nd dash (“-“) in cell A2, you need to embed another FIND function in 3rd argument (start_num) of FIND function, like this;
FIND("-",A2)+1)
For 3rd occurrence the formula would be like followings;
=FIND("-",A2,FIND("-",A2,FIND("-",A2)+1)+2)
To locate 3rd dash (“-“) in cell A2, you need to embed three FIND functions like above. In the 3rd argument (start_num) of 2nd FIND function, you need to embed another FIND function like this;
FIND("-",A2)+1)+2)
Extract N characters following the Nth occurrence of a character
Using FIND function within MID function you can extract the N characters following the Nth occurrence of a specified character. Suppose you want to extract 3 characters after the 2nd occurrence of dash character “-“. To do this you need to use FIND function within MID function to extract 3 characters in following formula;
=MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,3)
MID function extracts N characters from a string based on start_num position of a specified character as per the following syntax;
MID(text, start_num, num_chars)
Here you need to find Nth occurrence of a specified character using FIND function in start-num argument of MID function and add 1 in the resulting Nth occurrence of that character in start_num argument, like followings;
FIND("-",A2,FIND("-",A2)+1)+1
Leave a Comment