Excel SEARCH Function and Formula with Examples


How to Use the SEARCH Function and Formula in Excel

In this tutorial, you'll learn how to use the SEARCH function and formula in Excel. 

SEARCH is a function to get the position of one text (find_text) within another text string (within_text).

SEARCH returns the number of the starting position of find_text in within_text

SEARCH() Syntax

The syntax of the SEARCH() function is as follows:

=SEARCH(find_text, within_text, [start_number])

Return Value

A number representing the starting position of find_text in within_text.

Arguments

  • find_text: The text you want to find.
  • within_text:  The string containing substring that you want to search within.
  • start_numberThe starting position in within_text. If start_number omitted, SEARCH() searches from the 1st position of within_text.

Remarks

  • start_number is optional and defaults to 1.
  • SEARCH() is not case sensitive and allows the wildcard characters, the question mark (?) and asterisk (*).
  • If find_text not found in within_textSEARCH() returns the #VALUE! (error value) 
  • If start_number is greater than the length of the within_textSEARCH() returns #VALUE!.
  • If start_number is not greater than zero, SEARCH() returns #VALUE!.

SEARCH() Function Examples

Example 1: Searching from the first position

The following demonstrates an example of searching for a text inside anther text starting from the first position:

=SEARCH("Tutorial", "This Is The Best Microsoft Excel Tutorial")

The above formula searches for text Tutorial from the 1st position of text "This Is The Best Microsoft Excel Tutorial" and returns 34.

Example 2: Searching from a specified position

To search for a substring inside a given string starting at the specified position, specify the start_number argument, as the following example shows:

=SEARCH("Tutorial", "This Is The Best Microsoft Excel Tutorial", 20)

The above formula searches for Tutorial, starting from the 20th position, and returns 34.

Let's take the previous formula and change start_number to 35, as the following example shows:

=SEARCH("Tutorial", "This Is The Best Microsoft Excel Tutorial", 35)

Nothing found the above formula returns #VALUE!.

Example 3: SEARCH() is not case-sensitive

The SEARCH function is not case-sensitive. The upper case and lower case characters are the same when performing a search.
Here is an example:

=SEARCH("tutorial", "This Is The Best Microsoft Excel Tutorial and Tips.")

The above formula searches for tutorial and returns 34, the starting position of Tutorial. 

Example 4: Partial Matching (*)

The following example shows how to perform partial matching by using (*):

=SEARCH("Excel*Function", "Microsoft Excel SEARCH Function Tutorial")

As you can see, (*) is placed between Excel and Function.

Excel*Function matches the following conditions in order:

  • Any text begins with Excel
  • Any sequence of characters
  • Ends with Function

The above formula returns 11, which is the position of the phase Excel Function. 

Example 5: Partial Matching (?)

You can also perform partial matching using the wildcard character (?).
Here is an example:

=SEARCH("Excel????Function", "Excel SEARCH Function Tutorial")

As you can see, there are four (?)s placed between Excel and Function

Excel???? Function matches the following conditions in order:

  • Any text begins with Excel
  • Any four characters
  • Ends with Function

Nothing found the above formula return #VALUE!.

In this tutorial, you've learned how to use the SEARCH function and formula in Excel. 

SEARCH is a function to find one text within another text and returns the position of the matched text. 

Unlike FIND(), SEARCH() is not case-sensitive and allows you to use the wildcard characters (*) and (?) in find_text to perform partial matching.

Leave a Reply

Your email address will not be published. Required fields are marked *