How to use the FIND function

What is the FIND function?

The FIND function returns the position of a specific string located in another string, reading left to right. Note, the FIND function is case-sensitive.

What is the difference between the FIND function and the SEARCH function?

The FIND and SEARCH functions are nearly identical, with one key difference: FIND is case-sensitive while SEARCH is not. FIND only locates exact matches taking into account uppercase and lowercase letters. SEARCH will find a text string regardless of the case used in the search term or within the searched text.

For example, FIND would not match "battery" in "Battery", while SEARCH would find it even with a mismatch of cases. In all other aspects, FIND and SEARCH work the same - they search within a text string and return the starting position of a matched search term.

What is the difference between the FIND function and the FINDB function?

The FIND and FINDB functions handle character counting differently based on your default language setting in Excel. FINDB is designed for double-byte languages like Chinese, Japanese, Korean. It counts each double-byte character as 2 when a DBCS language is set as the default.

FIND always counts each character as 1, single-byte or double-byte, regardless of language setting. So FINDB will return larger position values than FIND for the same text in a DBCS language. The difference accounts for the double-byte characters taking 2 positions.

Japanese, Chinese (Simplified), Chinese (Traditional), and Korean are some examples of languages supporting DBCS.

What is DBCS?

It is an abbreviation of double-byte character set.

1. FIND Function Syntax

FIND(find_text,within_text, [start_num])

2. FIND Function Arguments

find_text	Required. Is the text you want to find. ? and * wildcard characters are allowed.
within_text	Required. This argument is the text in which you want to search for.
[start_num]	Optional. The character number in within_text, counting from the left at which you want to start searching. If omitted 1 is used.

3. FIND Function example

The image above demonstrates the FIND function in cells D3 and D4. Cell D3 shows the FIND function searching for the text "friend" within the text string "Hello friend".

Formula in cell C3:

The formula returns 7 , "friend" is found at character number 7 in text string "Hello friend". FIND is case-sensitive, so it will only match the lowercase "friend".

Cell D4 demonstrates what happens if no match is found, FIND will return a #VALUE! error instead of a number. Test for errors using IFERROR() to provide user-friendly feedback. Here is an example of combining the FIND function and the IFERROR function:

Formula in cell D4:

The formula above shows "Not found!" in cell D4 if the specified search value in cell C4 is not found at all in cell B4.

Back to top

4. FIND Function not working

If the text string is not found the FIND function returns a #VALUE! error. To avoid the error you have several options:

• The ISNUMBER function returns boolean values TRUE or FALSE, like this:
  =ISNUMBER(FIND(C3,B3))
• The IFERROR function to display a custom error message, demonstrated in section 3 above.
• The ISERROR function returns TRUE if the FIND function output is an error value and FALSE if not.

The #NAME error appears if you misspelled the FIND function.

Back to top

5. FIND function returns all positions of a given substring

The FIND function returns the position of a specific string located in another string, reading left to right. Note, the position of the first instance is returned, the others are not displayed.

This example demonstrates a formula that extracts all positions of all instances of a specific substring in another string also considering upper and lower letters. FIND function is not even used in this formula.

Formula in cell E3:

Cell E3 displays 2 and 10 which are the positions of "e" in "Hello friendly Elf" reading from left to right. The formula works fine with larger substrings as well.

Explaining formula

Step 1 - Count characters

The LEN function returns the number of characters in a cell value.

Function syntax: LEN(text)

LEN(B3)

becomes

LEN("Hello friendly Elf")

and returns 18.

Step 2 -  Create a sequence from 1 to n

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(,LEN(B3))

becomes

SEQUENCE(,18)

and returns

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}

Step 3 -  Split string based on character count of substring

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(B3,SEQUENCE(,LEN(B3)),LEN(C3))

becomes

MID("Hello friendly Elf",{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18},1)

and returns

{"H","e","l","l","o"," ","f","r","i","e","n","d","l","y"," ","E","l","f"}

Step 4 - Compare array to substring

The EXACT function checks if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters.

Function syntax: EXACT(text1, text2)

EXACT(MID(B3,SEQUENCE(,LEN(B3)),LEN(C3)),C3)

becomes

EXACT({"H","e","l","l","o"," ","f","r","i","e","n","d","l","y"," ","E","l","f"},"e")

and returns

{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

Step 5 -  Filter sequence based on boolean values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(SEQUENCE(,LEN(B3)),MID(B3,SEQUENCE(,LEN(B3)),LEN(C3))=C3)

becomes

FILTER({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18},{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE})

and returns

{2,10}

Step 6 -  Join strings

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(",",1,FILTER(SEQUENCE(,LEN(B3)),MID(B3,SEQUENCE(,LEN(B3)),LEN(C3))=C3))

becomes

TEXTJOIN(",",1,{2,10})

and returns

"2,10".

Step 7 -  Simplify formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

=TEXTJOIN(",",1,FILTER(SEQUENCE(,LEN(B3)),EXACT(MID(B3,SEQUENCE(,LEN(B3)),LEN(C3)),C3)))

z - SEQUENCE(,LEN(B3))

=LET(z,SEQUENCE(,LEN(B3)),TEXTJOIN(",",1,FILTER(z,EXACT(MID(B3,z,LEN(C3)),C3))))

Useful resources

FIND function - Microsoft Support
Excel FIND and SEARCH functions with formula examples

'FIND' function examples

Functions in 'Text' category

The FIND function function is one of 29 functions in the 'Text' category.

Excel function categories

Excel categories

Leave a Comment