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
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 10...
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
At the highest level, the MID function simply extracts the file...
In the example, cell C5 contains this formula:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
At the core, this formula uses...
The cell function is used to get the full file name and path:
CELL("filename",A1)
The result looks like this:
path[workbook.xlsm]sheet
The full file name and path are fed into the LEFT function, which...
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...
This formula uses the LOOKUP function to find and retrieve the last matching file name. The lookup value is 2, and the lookup_vector is created with this:
1/(ISNUMBER(FIND(G6,files)))
Inside this snippet, the FIND...
Overview
The formula looks complex, but the mechanics are in fact quite simple.
As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the...
At the core, this formula uses the fact that the SUBSTITUTE function understands "instance", supplied as an optional fourth argument called "instance_num". This means you can use the SUBSTITUTE function to replace a...
At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with...
The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string ("") for the "new_text" argument.
In this case, we want to remove the labels that appear inside text....
In this example the goal is to parse feet and inches out the text strings shown in column B, and create a single numeric value for total inches. The challenge is that each of the two numbers is embedded in text. The...
The FIND function returns the position of the text when it's found in the cell, and the #VALUE! error if not. In the example, the cell B4 contains the number "2" in the 29th position. So, if we just used this formula...
This formula uses the MID function to remove the last word from a text string. The main challenge is to figure out where the last word begins.
The formula is a bit convoluted, but the steps are simple. We first count...
At the core, this formula extracts characters from the right with the RIGHT function, using FIND and LEN to figure out how many characters to extract. C4 contains the text "achang@maaker.com", so LEN returns 17...
The SEARCH function returns the position of the text you are looking for as a number (if it exists). Conditional formatting automatically treats any positive number as TRUE, so the rule is triggered whenever search...
Related videos
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.
In this video we look at how to use the FIND and SEARCH function to find the position of a substring in 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.
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
