Home » Excel-Built-In-Functions » Excel-Search-Function

Basic Description

The Excel SEARCH function returns the position of a specified character or string within a supplied text string. The function is not case-sensitive.

The format of the function is:


where the arguments are as follows:

search_text	the character or string that you wish to search for
within_text	the original text, that should be searched
start_num	the character number from which the search should begin

Note: This function is NOT case-sensitive. If you want a case-sensitive search, try the FIND function.

The SEARCH function will return an error if the supplied search_text is not found. If the supplied search_text is found, the function returns a number, representing the position of the search_text in the within_text string.

Example

The following example shows the Excel SEARCH function applied to the values in cells B1 - B4 of the example spreadsheet.

	A	B	C
1	'SEARCH' for the letter "t", with the start_num argument unspecified (default value = 1):	Original Text	=SEARCH("t",B1)
2	'SEARCH' for the letter "i", with the start_num argument unspecified (default value = 1):	Original Text	=SEARCH("i",B2)
3	'SEARCH' for the letter "i", with the start_num argument set to 4:	Original Text	=SEARCH("i",B3, 4)
4	'SEARCH' for string "in", with the start_num argument unspecified (default value = 1):	Original Text	=SEARCH("in",B4)

The results of the 4 calls to 'SEARCH' are shown in the spreadsheet below.

	A	B	C
1	'SEARCH' for the letter "t", with the start_num argument unspecified (default value = 1):	Original Text	10
2	'SEARCH' for the letter "i", with the start_num argument unspecified (default value = 1):	Original Text	3
3	'SEARCH' for the letter "i", with the start_num argument set to 4:	Original Text	5
4	'SEARCH' for string "in", with the start_num argument unspecified (default value = 1):	Original Text	5

Note that, in the first example, the search function returns the position of the upper case "T", even though the search_text is a lower case "t".

Also note the third example (in cell C3), in which the start_num is set to 4. In this case the search begins at the fourth character of the within_text string. Therefore, the result returned is position 5.

Example 2 - Use SEARCH to Pick Out Cells Containing Specific Text

The following example shows the Excel SEARCH function used to highlight cells containing a specific text string.

Imagine you have a spreadsheet showing members of staff alongside a list of skills. You want to pick out the rows containing members of staff who have typing skills. One way to do this is to use the SEARCH function to search for the word "Typing" in the 'Skills' column. This is shown in the spreadsheet below.

	A	B	C
1	Name	Skills	Typing
2	Jane SMITH	Shorthand, Typing, Visual Basic, Excel	=SEARCH(C$1,B2)
3	Julie KENNEDY	Presentation Skills, Management Training	=SEARCH(C$1,B3)
4	DOMINIC TAYLOR	Microsoft Office, typing	=SEARCH(C$1,B4)
5	James COOPER	Typing, HTML Programming	=SEARCH(C$1,B5)

Note that, in the example above, the search_text is set to C$1, (ie. the string "Typing").

When the string "Typing" is found, the function returns the position of the start of this string within the searched cell. If the string "Typing" is not found, the function returns an error.

	A	B	C
1	Name	Skills	Typing
2	Jane SMITH	Shorthand, Typing, Visual Basic, Excel	12
3	Julie KENNEDY	Presentation Skills, Management Training	#VALUE!
4	DOMINIC TAYLOR	Microsoft Office, typing	19
5	James COOPER	Typing, HTML Programming	1

Note that the SEARCH function 'searches' for the string "typing" within cell B4, even though it begins with a lower case "t". This is because the Excel SEARCH function is not case sensitive. If you need to do a case-sensitive search, you should use Excel's FIND function.

You might want to tidy up the results of the function in the example above. This can be done using the IF and ISERROR functions. For example, the formula in cell C2 could be written as


This would return the value "1" if the text "Typing" was found in cell B2 and "0" otherwise.