2. Excel Functions
  3. Excel Text Functions
  4. SEARCH

SEARCH

Excel Function: SEARCH

Purpose

The SEARCH function searches for and returns the location of one text value in another text value. The search is not case-sensitive, traverses the text value being searched from left to right and begins from the character whose position matches the value passed to the start_num argument. It ends either (1) when the first instance of the text value being searched for is discovered, or (2) the end of the text value being searched is reached. Upon discovering the first instance of the text value being searched for, the SEARCH function returns a whole number whose value is ≥ 1, which represents the position of its first character in the text value that was searched. #VALUE! is returned if the search fails to discover any instances of a text value being searched for.

Invocations of the SEARCH function take the form:

SEARCH(find_text,within_text,[start_num])

Example

Consider a cell containing the formula:

=SEARCH("def","ABC dEf ABC DeF",9)

The formula instructs the SEARCH function to search the text value “ABC dEf ABC DeF” for instances of “def” and to begin its search from the ninth character (“A“). Starting with this character, 3 characters are retrieved (“ABC“). “ABC” and “def” are not equal and another 3 characters are retrieved starting from the tenth character (“BC “) and they are compared to “def“. This process of retrieving 3 characters and performing a case-insensitive comparison between them and “def” repeats until either a match is found or it isn’t possible to retrieve 3 characters. Each time the process repeats, the starting character is moved one position to the right. The process is illustrated below:

Step 1, SEARCH advances to the 9th character in “ABC dEf ABC DeF” and extracts characters 9, 10 & 11 which form the text value “ABC” - this does not match “def”. Step 2, SEARCH advances to the 10th character in "ABC dEf ABC DeF" and extracts characters 10,11 & 12 which form the text value "BC " - this does not match “def”. Step 3, SEARCH advances to the 11th character in "ABC dEf ABC DeF" and extracts characters 11, 12 & 13 which form the text value "C D" - this does not match “def”. Step 4, SEARCH advances to the 12th character in "ABC dEf ABC DeF" and extracts characters 12,13 & 14 which form the text value " De" - this does not match “def”. Step 5, SEARCH advances to the 13th character in "ABC dEf ABC DeF" and extracts characters 13,14 & 15 which form the text value "DeF" - this matches “def” because SEARCH ignores differences in case when comparing values. Step 6, the number 13 is returned by the SEARCH function, because this is the position of the first character in the "def" located by the SEARCH function.

Arguments

ArgumentArgument typeDescription
find_textMandatoryA text value that will be searched for in the value supplied to the within_text argument. the SEARCH function permits two wildcard characters and two escape sequences to appear in find_text. They are described in the table below.
ValueDescription
*Represents any sequence of characters of any length. For example, “a*f” will match with a text value beginning with “a“, followed by any sequence of characters of any length and ending with “f“.
?Represents any one character. For example, “a?f” will match with a text value beginning with “a“, followed by any character and ending with “f“.
~*An escape sequence which matches the character “*“. For example, “a~*b” matches text value “a*b“.
~?An escape sequence which matches the character “?“. For example, “a~?b” matches text value “a?b“.
within_textMandatoryA text value searched for instances of the value supplied to the find_text argument.
start_numOptional. Defaults to 1 if omitted.The position of the character in within_text from which the search for find_text will begin.

Related Functions

FunctionExamplePurpose
FINDFIND(find_text,within_text,[start_num])Its purpose is identical to that of the SEARCH function: the FIND function searches for and returns the location of one text value in another text value. FIND differs from SEARCH in that it performs case-sensitive searches and does not support wildcards.

Error Conditions

Error valueCondition typeCondition
#VALUE!Bad argumentThe value supplied to the start_num argument was ≤ 0.
#VALUE!Bad argumentThe value of start_num was greater than the number of characters in within_text.
#VALUE!RuntimeThe text value find_text could not be found in within_text.

Examples

ExampleFormulaResultDescription
1=SEARCH("abc","ABC abc",1)1Find the first instance of “abc” that occurs from character 1 onwards in “ABC abc“.
  • The SEARCH function matches:
    • “a” to character 1 (“A“).
    • “b” to character 2 (“B“).
    • “c” to character 3 (“C“).
  • Characters 1,2 & 3 of “ABC abc” match “abc“.
  • The SEARCH function returns 1.
2=SEARCH("abc","abc ABC",2)5Find the first instance of “abc” that occurs from character 2 onwards in “abc ABC“.
  • The SEARCH function matches:
    • “a” to character 5 (“A“).
    • “b” to character 6 (“B“).
    • “c” to character 7 (“C“).
  • Characters 5,6 & 7 of “abc ABC” match “abc“.
  • The SEARCH function returns 5.
3=SEARCH("","abc",2)2Find the first instance of “” (the empty text value) that occurs from character 1 in “abc“.
  • The search begins from character 2 of “abc” (b).
  • The empty text value between “b” and “c” matches the empty text value.
  • The SEARCH function returns the number 2.
  • This demonstrates that:
    • SEARCH treats the empty text value as a character.
    • The position of the preceding non-empty character is returned.
4=SEARCH("a*e","abcde ABCDE",5)7Find the first instance of “a*e” that occurs from character 5 onwards in “abcde ABCDE“.
  • “a*e” contains the wildcard character “*“.
  • “a*e” represents a text value:
    • Beginning with “a“
    • Followed by any sequence of characters of any length.
    • Ending with “e“.
  • The SEARCH function matches:
    • “a” to character 7 (“A“).
    • “*” to characters 8,9 & 10 (“BCD“).
    • “e” to character 11 (“C“).
  • Characters 7,8,9,10 & 11 of “abcde ABCDE” match “a*e“.
  • The SEARCH function returns 7.
5=SEARCH("a~*c","A*C abc",1)1Find the first instance of “a~*c” that occurs from character 1 onwards in “A*C abc“.
  • “a~*c” contains the escape sequence “~*“.
  • “a~*c” represents a text value:
    • Beginning with “a“.
    • Followed by a single instance of “*“.
    • Ending with “c“.
  • The SEARCH function matches:
    • “a” to character 1 (“A“).
    • “~*” to character 2 (“*“).
    • “c” to character 3 (“C“).
  • Characters 1,2 & 3 of “A*C abc” match “a~*c“.
  • The SEARCH function returns 1.
6=SEARCH("a?c","ABC abc",1)1Find the first instance of “a?c” that occurs from character 1 onwards in “ABC abc“.
  • “a?c” contains the wildcard character “?“.
  • “a?c” represents a text value:
    • Beginning with “a“
    • Followed by any one character.
    • Ending with “c“.
  • The SEARCH function matches:
    • “a” to character 1 (“A“).
    • “?” to character 2 (“B“).
    • “c” to character 3 (“C“).
  • Characters 1,2 & 3 of “ABC abc” match “a?c“.
  • The SEARCH function returns 1.
7=SEARCH("a~?c","A?C abc",1)1Find the first instance of “a~?c” that occurs from character 1 onwards in “A?C abc“.
  • “a~?c” contains the escape sequence “~?“.
  • “a~?c” represents a text value:
    • Beginning with “a“.
    • Followed by a single instance of “?“.
    • Ending with “c“.
  • The SEARCH function matches:
    • “a” to character 1 (“A“).
    • “~?” to character 2 (“?“).
    • “c” to character 3 (“C“).
  • Characters 1,2 & 3 of “A?C abc” match “a~?c“.
  • The SEARCH function returns 1.
8=SEARCH("a*e?","abcde abcdef",1)1Find the first instance of “a*e?” that occurs from character 1 onwards in “abcde abcdef“.
  • “a*e?” contains the wildcard characters “*” and “?“.
  • “a*e?” represents text value:
    • Beginning with “a“.
    • Followed by any sequence of characters of any length.
    • Followed by “e“.
    • Ending with any one character.
  • The SEARCH function matches:
    • “a” to character 1 (“a“).
    • “*” to characters 2,3 & 4 (“bcd“).
    • “e” to character 5 (“e“).
    • “?” to character 6 (” “).
  • Characters 1,2,3,4,5 & 6 of “abcde abcdef” match “a*e?“.
  • The SEARCH function returns 1.

Examples: Errors

ExampleFormulaResultDescription
1=SEARCH("abc","ABC abc",0)#VALUE!Find the first instance of “abc” that occurs from character 0 onwards in “ABC abc“.
  • Because no character can exist at position 0 in a text value, #VALUE! is returned.
2=SEARCH("a","aaaaa",6)#VALUE!Find the first instance of “a” that occurs from character 6 onwards in “aaaaa“.
  • Because no character 6 exists in “aaaaa”, #VALUE! is returned.
3=SEARCH("z","abc",1)#VALUE!Find the first instance of “z” that occurs from character 1 onwards in “abc“.
  • Because “z” does not exist in “abc”, #VALUE! is returned.