What to Know
- The FIND function is used to find the position of a given string from a selection.
- It can be used on its own but it's more often nested within other functions including LEFT, RIGHT, and MID.
This article explains how to use the FIND function on its own and nested with other functions in any version of Excel including Excel 2019 and Microsoft 365.
What is the FIND Function?
The FIND function in Excel finds the position of a given string from a particular selection.
Excel's FIND function can be used on its own to produce the character's position, like in the image below, but it's more often nested within other functions. When you nest it with LEFT, RIGHT, and MID, you can extract and delete pieces of information from a cell.
The Find and Replace feature, not this function, is used to search through an Excel workbook.
FIND Function Syntax & Arguments
Write the function like this for Excel to properly understand it:
=FIND(find_text, within_text, [start_num])
- find_text: This is the text you want to find. It's required.
- within_text: This is the location that contains the text you want to find. This is also required.
- start_num: This is the first character to start the search from; if omitted, 1 is used. This argument is optional.
Here's more information on the FIND function arguments:
- It's case sensitive.
- Wildcard characters aren't allowed.
- #VALUE! is returned in several situations: if the text you're searching for doesn't appear in within_text, if start_num isn't greater than zero, and if start_num is greater than the length of within_text.
The SEARCH function is really similar but it isn't case sensitive and it does allow wildcards.
FIND Function Examples
Here are some of the different ways you can use the FIND function:
Letter in the Formula
=FIND("w",A2)
In this example of the FIND function, we're looking for the position of w within cell A2. Given that the cell reads Lifewire, the result of this formula is 5.
Letter Referenced in Another Cell
=FIND(D2,A2)
This is a very similar example but the letter we're using in the search is stored in D2. If w were written in D2, this would produce the same result as the first example.
Those first two examples show the basics of the FIND function. The numbers they produce are used by Excel to calculate what to do next, which becomes useful when you combine it with other functions...
Extract First Name With LEFT Function
=LEFT(A2,FIND(" ",A2))
This example is using the LEFT function with FIND so that we can extract someone's first name from a cell that contains their first and last name. Since a space is being used to separate the first and last name, we're using the FIND function to locate the space in A2. Excel understands the space as being in the sixth position, so now the LEFT function can grab everything to the left of the sixth place. In this case, it's the first name Emily.
Extract Last Name With RIGHT Function
=RIGHT(A14,FIND(" ",A14)-2)
A very similar function could be used to get the last name in this example. But since we want what's to the right of the character FIND is locating (the space), we use the RIGHT function.
The subtraction at the end is to offset the function by two characters. We only want the last name, not any letters of the first name or the space, so we change the formula slightly to start at a different position.
Add Text to FIND Formula
="My first name is "&LEFT(A14,FIND(" ",A14))&"and my last name is "&RIGHT(A14,FIND(" ",A14)-2)&"."
This is a fun example of the FIND function where we're combining the two formulas we just went over. We're basically converting the one cell that has the first and last name into a cell that includes both names but also forms a sentence.
Extract Number From Cell With MID Function
=MID(A16,FIND("(",A16)+1,(FIND(")",A16)-FIND("(",A16))-1)
This example of the FIND function is bit more complex. It uses the MID function to isolate what's between the parentheses by first identifying the locations of the left and right parenthesis.