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 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 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 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 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...
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 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...
Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that begin with specific text. However, if you want more flexibility, you can use...
If you want to highlight rows in a table that contain specific text, you use conditional formatting with a formula that returns TRUE when the the text is found. The trick is to concatenate (glue together) the columns...
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...
Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that contain specific text. However, if you want more flexibility, you can use your...
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...
T extract the domain from an email address, you can use a formula based on the RIGHT, LEN, and FIND functions. In the generic form above, email represents the email address you are working with. In the example shown,...
If you have a list of things (words, substrings, etc) and want to find out which of these things appear in a cell, you can build a simple table and use a formula based on the SEARCH function.
Setup
Suppose you have a...
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
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 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.
Related functions
