Excel FIND Function
Summary
The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.
Purpose
Get the location of text in a string
Return value
A number representing the location of find_text.
Syntax
=FIND (find_text, within_text, [start_num])
Arguments
- find_text - The text to find.
- within_text - The text to search within.
- start_num - [optional] The starting position in the text to search. Optional, defaults to 1.
Version
Usage notes
The FIND function returns the position (as a number) of one text string inside another. If there is more than one occurence of the search string, FIND returns the position of the first occurrence. FIND does not support wildcards, and is always case-sensitive. Use the SEARCH function to find the position of text without case-sensitivity and with wildcard support. When the text is not found, FIND returns a #VALUE error. Also note, when find_text is empty, FIND returns 1.
Examples
To return the position of the letter "A" in the word "Apple":
=FIND("A","Apple") // returns 1
Note hardcoded text values must be enclosed in double quotes ("").
To find the position of the lowercase "the":
=FIND("the","The cat in the hat") // returns 12
Error if not found:
=FIND("Cat","The cat in the hat") // returns #VALUE!
Because FIND is case-sensitive, "Cat" (with uppercase "C") is not found and FIND returns a #VALUE! error.
Notes
- The FIND function will return the location of the first instance of find_text in within_text.
- The location is returned as the number of characters from the start of within_text.
- Start_num is optional and defaults to 1.
- FIND returns 1 when find_text is empty.
- FIND will return #VALUE if find_text is not found in within_text.
- FIND is case-sensitive and does not support wildcards.
- Use the SEARCH function to search without case-sensitivity and/or to use wildcards.
FIND formula examples
The FIND function finds the first space character (" ") in the name and returns the position of that space in the full name. The number 1 is subtracted from this number to account for the space itself. This number is...
Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the FIND function is configured to search for the text "XST" in cell C5. If found, FIND will return a numeric position (i....
We need a way to split text at a certain marker that corresponds to a certain number of words. Excel doesn't have a built-in function to parse text by word, so are using the SUBSTITUTE function's "instance" argument to...
At the core, this formula uses the RIGHT function to extract characters starting from the right. The other functions which make up the complex part of this formula just do one thing: they calculate how many characters...
The first formula uses the FIND function to locate the underscore(_) in the text, then we subtract 1 to move back to the "character before the special character":
FIND("_",B5)-1 // returns 6
In this example...
When you count cells with "OR logic", you need to be careful not to double count. For example, if you are counting cells that contain "abc" or "def", you can't just add together two COUNTIF functions, because you may...
When you sum cells with "OR" criteria, you need to be careful not to double count when there is a possibility that both criteria will return true. In the example shown, we want to sum values in Column C when cells in...
The core of this formula is the MID function, which extracts the text in a URL starting with the character after "//", and ending with the character before the trailing slash ("/"):
=MID(url,start,chars)
The url...
The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period (".").
=LEFT(filename,characters)
The FIND...
The COUNTIF function counts cells in a range that meet criteria. For example, to count the number of cells in a range that contain "a" you can use:
=COUNTIF(range,"a") // exact match
However, note this is...
At the core, this formula uses the MID function to extract characters starting at the second to last space. The MID function takes 3 arguments: the text to work with, the starting position, and the number of characters...
Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When a formula returns FALSE, validation fails and the...
B4 contains the URL: "https://exceljet.net/keyboard-shortcuts"
At the core, this formula is extracting characters from the URL, starting from the left, and using the FIND function to figure out how many characters to...
The CELL function is used to get the full file name and path:
CELL("filename",A1)
The result looks like this:
path[workbook.xlsm]sheetname
CELL returns this result to the MID function as the text...
Working from the inside out, this part of the formula searches each cell in B4:B11 for all values in the named range "things":
--ISNUMBER(SEARCH(things,B4)
The SEARCH function returns the position of the value if...
Related videos
In this video, we look at how to use the FIND and SEARCH function to find the position of a substring in a another string.
Some Excel formulas are complex. But all complex formulas are built from simple steps. In this video, we built a more complex formula step by step.
In this video, we look at how to combine the FIND function with the LEFT function to extract the first name from a full name using a formula.
Related functions
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
