A to Z of Excel Functions: The SEARCH Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the SEARCH function.

The SEARCH function

The SEARCHfunction locates one text string within a second text string and returns the number in respect of the starting position of the first text string from the first character of the second text string.  For example, to find the position of the letter "P" in the word "SumProduct", you can use the following function:

It is not case sensitive.  This function returns 4 because "P" is the fourth character in the text "SumProduct".  You can also search for words within other words, e.g. the formula

returns 5, because the word "she" begins at the fifth character of the word "worksheet".  It employs the following syntax to operate:

The SEARCH function has the following arguments:

• find_text: this is required and represents thetext you wish to find
• within_text: this is also required.  This is the text in which you want to search for the value of the find_text argument
• start_number: this argument is optional and denotes the character number in the within_text argument at which you want to start searching.

It should be further noted that:

• as stated above, the SEARCH function is not case sensitive.  If you want to create a case sensitive search, use FIND
• you may use wildcard characters, such as the question mark (?) and asterisk (*), in the find_text argument:
  • a question mark matches any single character
  • an asterisk matches any sequence of characters
  • if you want to find an actual question mark or asterisk, type a tilde (~) before the character

• if the value of find_text is not found, the #VALUE! error value is returned  
• if the start_number argument is omitted, it is assumed to be 1
• if start_numberis not greater than zero [0] or is greater than the length of the within_text argument, the #VALUE! error value is returned
• use start_number to skip a specified number of characters.  The SEARCH function always returns the number of characters from the start of the within_text argument, counting the characters you skip if the start_number argument is greater than one [1].

Please see my examples below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.