Home > Formulas > FIND Function

Excel FIND Function

These Excel FIND function examples show how to look for a text string (case sensitive), within another text string, and return its position, if found. For non-case-sensitive searches, use the SEARCH function.

Note: To look for values on the worksheet, see the Find and Replace page

Author: Debra Dalgleish

Video: FIND Function

The Excel FIND function looks for a case-sensitive text string, within another text string, and returns its position, if found. Watch this short video to see 3 examples of how to use FIND. There are written steps below the video.

Uses for FIND

The FIND function looks for a case-sensitive text string, within another text string, and it can:

• Find a text string within another text string – case sensitive
• NOTE: For non-case-sensitive searches, use the SEARCH function
• Find exact values from a list
• Find street name in address

FIND Syntax

The FIND function has the following syntax for its arguments:

• FIND(find_text,within_text,[start_num])
  • find_text is the text that you're looking for.
  • within_text is the string that you're searching in.
  • if start_num is omitted, the search starts with the first character

FIND Traps

The FIND function has a few traps:

• The FIND function will return the position of the first matching string, and it is case sensitive.
• If you need a non-case-sensitive search, use the Excel SEARCH function
• You can’t use wildcard characters in the find_text string. For wildcards, use the SEARCH function

Example 1: Find Text in String

To find specific text in a text string, you can use the FIND function. It is case sensitive, so in the screen shot below, the first two “i” characters are ignored, because they are lower case.

• =FIND(B5,B2)

Tip: To handle errors, if the text is not found, you can wrap the FIND function with the IFERROR function.

• =IFERROR(FIND(B5,B2),”Not Found”)

Ex 2: Find Exact Values from List

Because FIND is case sensitive, you can use it to find exact strings in another string.

In this example, there are valid codes listed in column E.

With the following FIND formula, you can identify any ID numbers in column B that contain one of the valid code strings.

• =IF(OR(ISNUMBER(FIND($E$2:$E$4,B2))),”Yes”,”No”)

Complete the Formula

• Excel 365 - press Enter to complete this formula.
• In earlier versions, press Ctrl + Shift + Enter (array entered)

Ex 3: Find Street Name in Address

In the next example, most of the addresses in column B start with a street number.

With the formula in column C, we check for a number in the first character.

If the first character is a number

• FIND function locates the first space character
• MID function returns all the text from the next character, to the end.

Here is the formula in cell C2:

• =IF(ISNUMBER(–LEFT(B2,1)), MID(B2, FIND(" ",B2)+1, LEN(B2)),B2)

Get the Sample File

To see the formulas used in these examples, download the FIND function sample workbook. The file is zipped, and is in Excel xlsx format, with no macros.

Excel Topics

Data Val | Pivot | Formulas | Macros | Format | Charts | Filters