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 location substring in a string

Return value 

A number representing the location of substring

Arguments 

  • find_text - The substring to find.
  • within_text - The text to search within.
  • start_num - [optional] The starting position in the text to search. Optional, defaults to 1.

Syntax 

=FIND(find_text, within_text, [start_num])

Usage notes 

The FIND function returns the position (as a number) of one text string inside another. If there is more than one occurrence of the search string, FIND returns the position of the first occurrence. When the text is not found, FIND returns a #VALUE error. Also note, when find_text is empty, FIND returns 1. FIND does not support wildcards, and is always case-sensitive. Use the SEARCH function to find the position of text without case-sensitivity and with wildcard support. 

Basic Example

The FIND function is designed to look inside a text string for a specific substring. When FIND locates the substring, it returns a position of the substring in the text as a number. If the substring is not found, FIND returns a #VALUE error. For example:

=FIND("p","apple") // returns 2
=FIND("z","apple") // returns #VALUE!

Note that text values entered directly into FIND must be enclosed in double-quotes ("").

Case-sensitive

The FIND function always case-sensitive:

=FIND("a","Apple") // returns #VALUE!
=FIND("A","Apple") // returns 1

TRUE or FALSE result

To force a TRUE or FALSE result, nest the FIND function inside the ISNUMBER function. ISNUMBER returns TRUE for numeric values and FALSE for anything else. If FIND locates the substring, it returns the position as a number, and ISNUMBER returns TRUE:

=ISNUMBER(FIND("p","apple")) // returns TRUE
=ISNUMBER(FIND("z","apple")) // returns FALSE

If FIND doesn't locate the substring, it returns an error, and ISNUMBER returns FALSE. 

Start number

The FIND function has an optional argument called start_num, that controls where FIND should begin looking for a substring. To find the first match of "the" in any combination of upper or lowercase, you can omit start_num, which defaults to 1:

=FIND("x","20 x 30 x 50") // returns 4

To start searching at character 5, enter 4 for start_num:

=FIND("x","20 x 30 x 50",5) // returns 9

Wildcards

The FIND function does not support wildcards. See the SEARCH function.

If cell contains

To return a custom result with the SEARCH function, use the IF function like this:

=IF(ISNUMBER(FIND(substring,A1)), "Yes", "No")

Instead of returning TRUE or FALSE, the formula above will return "Yes" if substring is found and "No" if not.

Notes

  • The FIND function returns the location of the first find_text in within_text.
  • The location is returned as the number of characters from the start.
  • Start_num is optional and defaults to 1.
  • FIND returns 1 when find_text is empty.
  • FIND returns #VALUE if find_text is not found.
  • FIND is case-sensitive but does not support wildcards.
  • Use the SEARCH function to find a substring with wildcards.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.