The SEARCH and SEARCHB functions locate one text string within a second text string and return return the number of the starting position of the first text string from the first character of the second text string. The functions are not case-sensitive.

Syntax

=SEARCH(find_text,within_text,[start_num])

=SEARCHB(find_text,within_text,[start_num])

Note: SEARCHB counts 2 bytes per character only when a double-byte character set (DBCS) language is set as the default language. Otherwise SEARCHB behaves the same as SEARCH, counting 1 byte per character.

Arguments

Argument Description
find_text The text that you want to find
within_text The text in which you want to search for the value of the find_text argument
[start_num] Optional. The character number in the within_text argument at which you want to start searching

  If omitted, this takes on the default value of 1, i.e. begin the search at the start of the within_text string

Note: If the supplied find_text is found, the SEARCH function returns a number, representing the position of the find_text in the within_text. If the supplied find_text is not found, the function returns the #VALUE! error.

Examples

  A B C D E F
1 Find Text Start Formula Result Notes
2 A Apple   =SEARCH(A2,B2) 1 Start number is optional; defaults to 1
3 p Apple 1 =SEARCH(A3,B3,C3) 2 Result is based on the first occurrence 
4 the The cat in the hat 1 =SEARCH(A4,B4,C4) 1 Search is NOT case sensitive
5 00?? 01-A-0010 1 =SEARCH(A5,B5,C5) 6 Search supports wildcards ? and *

Usage note: You can also use the following wildcards in the find_text argument:

      ?  –  matches any single character
  *  –  matches any sequence of characters

eg. the condition “A*e” will match any substring beginning with “A” and ending in “e”.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  the supplied find_text is not found in the supplied within_text string
  the supplied [start_num] is less than zero or is greater than the length of the supplied within_text string