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 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 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
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 gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.
Working from 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...
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 cell in B4:G12...
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....
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 XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2.
In the example shown, XLOOKUP is configured to match the value entered...
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...
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...
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...
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...
In the first part of the formula, feet are extracted and converted LEFT and FIND like this:
=LEFT(B5,FIND("'",B5)-1)*12
Working from the inside out, FIND is used to locate the position of the single quote...
In this example we are looking for the nth occurrence of the "@" character.
Working from the inside out, we first use the SUBSTITUTE function to replace the nth occurrence of "@" with CHAR(160):
SUBSTITUTE(B5,...
Background
A common annoyance with data is that it may be represented as text instead of numbers. This is especially common with dimensions, which may appear in one text string that includes units, for example:
50 ft...
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...
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.
