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.
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
To lookup the latest file version in a list, you can use a formula based on the LOOKUP function together with the ISNUMBER and FIND functions. In the example shown, the formula in cell G7 is:
=LOOKUP(2,1/(ISNUMBER(...
To find the nth occurence of a character in a text string, you can use a formula based on the FIND and SUBSTITUTE functions.
In the example shown, the formula in D5 is:
=FIND(CHAR(160),SUBSTITUTE(B5,"@",...
To count cells that contain either one value or another, you an either use a helper column then tally up the count, or a more complex single cell formula.
Background
When you count cells with "OR" criteria, you need...
To extract the first name from a full name in "Last, First" format, you can use a formula that uses RIGHT, LEN and FIND functions. In the generic form of the formula (above), name is a full name in this format:
LAST,...
To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user...
To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or...
To generate basic outline numbering you can use a formula based several Excel functions, including COUNTA, IF, MID, FIND, and LEN. In the example shown, the formula in D5 is:
=COUNTA($B$5:B5)&"."&IF(...
To separate text and numbers, you can use a formula based on the FIND function, the MIN function, and the LEN function with the LEFT or RIGHT function, depending on whether you want to extract the text or the number. In...
If want to extract the name part of an email address, you can do so with a formula that uses the LEFT and FIND functions. In the generic form above, email represents the email address you are working with.
In the...
To check if a cell contains specific text, you can use the SEARCH function together with the ISNUMBER function. In the generic version, substring is the specific text you are looking for, and text represents text in the...
If you need to extract the first word from some text you can use a formula that uses the FIND and LEFT functions. From the example, the formula looks like this:
=LEFT(B4,FIND(" ",B4)-1)
How this formula...
If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look...
To remove a file extension from a file name, you can use a formula based on the LEFT and FIND functions. In the example shown, the formula in C5 is:
=LEFT(B5,FIND(".",B5)-1)
How this formula works
The core...
If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function.
How the formula works
The cell...
If you need extract the first name from a full name, you can easily do so with the FIND and LEFT functions. In the generic form of the formula (above), name is a full name, with a space separating the first name from...
Related videos
Some formulas you see in Excel can be extraordinarily complex. But all complex formulas are built from simple steps. In this video, we built a more complex formula step by step.
Related functions
Good links
