Excel SEARCH and You Will FIND

In Excel there are two almost identical functions; SEARCH and FIND.

The SEARCH and FIND functions allow you to find the location of text within a string of text.

They’re identical because they perform the same function except the FIND function is case sensitive, whereas the SEARCH function is not, and SEARCH allows the use of wildcards whereas FIND does not.

So let’s look at how we can use them.

SEARCH Function

The syntax for the SEARCH function is:

=SEARCH(find_text, witin_text, [start_num])

In English:

=SEARCH(find the location of my text or my text string, within a cell containing text or a text string, starting from position x)

Note: if the [start_num] is omitted it will start at 1.

For example; if cell A1 contains the words ‘went away’ and your formula reads

=SEARCH(’E’,A1)

The result will be 2, since the letter ‘e’ is the second letter in cell A1.

If you wanted to search for a text string, let’s say ‘away’

=SEARCH(’away’,A1)

The formula would return the result 6, since the word ‘away’ starts in position 6 in cell A1.

SEARCH Function Examples

SEARCH Function Examples Explained:

1. You’ll notice in the first example the SEARCH function ignored the fact that we were searching for an upper case ‘E’ and still returned a 2. If you want case sensitivity use the FIND function.
2. In the second example we put the start_num as ‘4’ and the result was 6.
3. In the third example we wanted it to return the starting position of a text string ‘edle’ so Excel ignored the first ‘e’ and returned the result ‘3’.
4. In the fourth example we used a question mark wildcard. The question mark wildcard will return the position of text that begins with an ‘e’, then has any single character, and ends in a ‘d’.
5. In the fifth example we used an asterisk wildcard. The asterisk wildcard enables us to search for any string that begins with an ‘h’ and ends in a ‘k’.
6. If the text string is not found the SEARCH function will return a #N/A result.

Note: you can’t use wildcards with the FIND function.

Whoop-de-doo I hear you say. What will I ever use that for? Ok, I hear you.

Some Cool Uses for SEARCH and FIND

On their own SEARCH and FIND aren’t much use so let’s look at some ways to use them nested in other formulas to unleash their power!

Caveat: I’ll briefly explain the tricks here, but for deeper understanding click here to download the workbook and play around with them yourself.

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.

2. Use with MID to parse columns as an alternative to Text to Columns

 


In cell C27: =SEARCH(” “,A27) returns the position of the first space, which is also the length of the first word (including the space).

In cell D27: =IFERROR(SEARCH(” “,$A$27,C27+1),LEN($A$27)+1)

Finds the location of the second space.

If the result is an error it returns the length of the whole text string + 1. This is simply error handling as the last word in the text string will return a #VALUE! error without this.

In cell C28: =MID($A$27,1,C27) returns the first seven characters in the cell A27.

In cell D28: =MID($A$27,C27,IFERROR(D27-C27,100)) Again, the IFERROR is simply error handling for the last word in the text string.

Now you can copy the formulas in D27 and D28 across your columns as many times as required, and Bob’s your Uncle!

Brief Explanation: This example uses the SEARCH function to locate the starting position of each word in cell A27.

It does this by finding the location of each space, and then adding 1 to get the starting position of each word.

It then uses those numbers in MID formulas in row 28 to extract the individual words from cell A27, and puts them in their own column. Just as you can do with Text to Columns.

This requires 4 different formulas, but once you’ve set them up you can copy them across your columns and quickly parse large amounts of text.

3. 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.

Tagged as: text function