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 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...
To find the nth occurrence 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,"@",...
If you need extract the last name from a full name, you can do so with this rather complex formula that uses several functions. In the generic form of the formula (above), name is a full name, with a space separating...
To count the number of cells that contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should contain, and "*"...
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...
To get the name of the current worksheet (i.e. current tab) you can use a formula based on the CELL function. CELL retrieves the workbook name and sheet, and the MID and FIND functions are used to extract just the...
To sum if cells contain either one text string or another (i.e. contain "cat" or "rat") you can use the SUMPRODUCT function.
Background
When you sum cells with "OR" criteria, you need to be careful not to double count...
To highlight cells that contain one of many text strings, you can use a formula based on the functions ISNUMBER and SEARCH, together with the SUMPRODUCT function. In the example shown, the conditional formatting applied...
If you want to get the current workbook's full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks...
To remove text from a cell when the text is at a variable position, you can use a formula based on the REPLACE function, with help from the FIND function.
In the example shown, the formula in C6 is:
=REPLACE(B6,1,...
To split a text string at a certain character, you can use a combination of the LEFT, RIGHT, LEN, and FIND functions.
In the example shown, the formula in C5 is:
=LEFT(B5,FIND("_",B5)-1)
And the formula in...
To remove the protocol (i.e. http://, ftp://, etc.) and trailing slash from a URL, you can use a formual based on the MID, FIND, and LEN functions. In the example shown, the formula in C5 is:
=MID(B5,FIND("//...
If you have worksheet that contains text dimensions (i.e. "50 ft x 200 ft" etc.) you can split the into two parts with formulas that use several text functions.
Background
A common annoyance with data is that it may...
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 extract the top level domain (called "TLD") from a list of domain names or email addresses, you can use a rather complex formula that uses several functions. In the generic form of the formula (above), domain...
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.
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 use the FIND and SEARCH function to find the position of a substring in a another string.
Related functions
Good links
