SEARCH function
Returns the number of the character at which a specific character or text string is found reading left to rigt (not case-sensitive)

SEARCH(find_text,within_text, [start_num])


find_text - Is the text you want to find. ? and * wildcard characters are allowed.

within_text - is the text in which you want to search for find_text

start_num - is the character number in within_text, counting from the left at which you want to start searching. Optional, if omitted 1 is used.

SEARCH function

Formula in cell C5:

=SEARCH($D$5, B5)

The function returns 11 in cell C5. Text string "blue" is found and begins in the 11-th character in "My car is blue". See cell range F5:S6.

I am using a absolute cell reference to cell D5 so I can copy the function to cell C11 without changing the argument. If you are interested in how absolute and relative cell references work, read this post:  Absolute and relative cell references

Formula in cell C11:

=SEARCH($D$5, B5)


=SEARCH("blue","My car is white")

and returns #VALUE!. Text string "blue" is not found in "My car is white".

FIND function
The function works the same as the search function except case-sensitive.

SEARCH function returns an array

SEARCH function array formula

Array formula in cell B5:B9: 



=SEARCH("is", {"My car is blue"; "My car is white"; "My house is grey"; "My bird was green"; "My dog is here"})

and returns {8; 8; 10; #VALUE!; 8} in cell range B5:B9. I have highlighted the beginning character in cell range D5:T9.

Download excel *.xlsx file

Search and Find functions.xlsx