Excel : SEARCH and FIND

Source from: http://www.myonlinetraininghub.com/excel-search-and-you-will-find

IF(ISNUMBER(SEARCH("brown",A20)),$F$20,$F$21)

1. Convert the result to a TRUE/FALSE answer using ISNUMBER and then use it in an IF statement to return a value for ‘brown foxes’ and a different value for everything else.


Brief Explanation: The formula in cell B20 is searching for the word ‘brown’, if the result is a number (ISNUMBER) then choose the value in cell F20, and if it’s not (if the word isn’t found SEARCH will return a #N/A error) then choose the value in cell F21.



1. Use it as an array formula to count the number of instances of a word occurring in text strings in a range of cells.

Note: the formula is entered =COUNT(IF(SEARCH(“brown”,A32:A35),1,”")) and then you press CTRL+SHIFT+ENTER to enter it as an array formula and Excel will enter the curly brackets for you.
If you liked this let me know by clicking the Facebook like, Tweet it or simply leave a comment below. I’d love to hear from you and how you use these functions.