Excel SEARCH Function

Posted byPete

What does it do?

The Excel SEARCH function allows you to find the starting position of a text string within another text string.  The text string to search is NOT case sensitive, it can be one character in length, a word or words and you may use wildcard characters for partial matching.  If found, it will return a whole number; this number represents the starting position of the text you attempted to search for, reading from left to right, if not found, it will return #VALUE!.

Syntax Explained

Excel SEARCH function in the formula bar showing syntax.

Find_text: This is the text you are looking for.

Within_text: This is the text string that find_text, may or may not appear in.

Start_num (optional): This is the position where the search should begin, if omitted, it will default to 1.  The 1 represents the first character in the within_text text string.

Examples

1. SEARCH covering many types.

Example of an Excel SEARCH function searching for the start position of a text string in another text string, some are found and one is not.

In the example above I have set up a few text strings to search for:

  • Row 3 = Searching for a “Blue” with UPPER case “B”, returns a 1, since this is the first character.
  • Row 4 = As above, but note the SEARCH function allows for lower case matching.  If you want to find and consider case matching too, use the FIND function.
  • Row 5 = Searching for a single letter which appears multiple times, as it’s reading from left to right it will return the FIRST occurrence, in this case it’s a 7.
  • Row 6 = As above but the letter is NOT found, therefore returns a #VALUE! return.  Check out the IFERROR function to allow for a descriptive message when a #VALUE! result is returned.
  • Row 7 = Using a question mark wildcard (?) you can use this for matching, this example returns a 2.
  • Row 8 and 9 = Searching for the letter “C” on its own and then using the asterisk (*).  The return values differ since the asterisk in front represents any number of characters preceding the “C”, hence it returns a 1, which differs to the row 8.

Pro Tip

Combining, or using alternatives to the SEARCH functions such as FIND, MID, LEN, RIGHT and LEFT can help support text extraction techniques.  When used correctly they can make light work of something that would take hours/days using manual methods.