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 allow only values that contain a specific text string, you can use data validation with a custom formula based on the FIND and ISNUMBER functions. In the example shown, the data validation applied to C5:C9 is:
=...
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 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 use XLOOKUP to match values that contain specific text, you can use wildcards and concatenation. In the example shown, the formula in F5 is:
=XLOOKUP("*"&E5&"*",code,quantity,"no...
To extract the last two words from a cell, you can use a formula built with several Excel functions, including MID, FIND, SUBSTITUTE, and LEN. In the example shown, the formula in C5 is:
=MID(B5,FIND("@",...
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...
If you want to extract the domain name from a complete URL, you can do so with a formula that uses the the LEFT and FIND functions. In the generic form above, url is the the URL you are working with.
In the example, we...
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 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 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 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 trim text to a certain number of words, you can use a formula based on the SUBSTITUTE, FIND, and LEFT functions. In the example shown, the formula in xxx is:
=LEFT(B5,FIND("#",SUBSTITUTE(B5,"...
To remove the last word from a text string, you can use a formula based on the MID function, with help from SUBSTITUTE, LEN, and FIND. In the example shown, the formula in cell B5 is:
=MID(B5,1,FIND("~",...
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 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...
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.
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.
Related functions
