Excel SEARCH Function

For example, suppose we want to search the “Thank” substring in the provided text or string “Thank you.” Here, we need to find the “Thank” word using the SEARCH function, which will return the word “Thank” location.

=SEARCH(“Thank,” B8). The output will be 1.

The SEARCH function is a text function used to find the location of a substring in a string/text.

The SEARCH function can be used as a worksheet function. It is not case-sensitive.

SEARCH Formula in Excel

Below is the SEARCH formula in Excel.

Explanation

Excel SEARCH function has three-parameter two (find_text, within_text) are compulsory parameters and one (start_num) is optional.

Compulsory Parameter:

  • find_text: find_text refers to the substring/character you want to search within a string or the text you want to find out.within_text:. Where your substring is located or where you perform the find_text.

Optional Parameter:

  • [start_num]:: from where you want to start the SEARCH within the text in excelSEARCH Within The Text In ExcelExcel provides the functionality that helps you to search for text in the file. You can use Find and Search function to search for a specific text cell value and arrive at the result.read more. If omitted, SEARCH considers it as 1 and star search from the first character.

How to Use the Search Function in excel? (with Examples)

The SEARCH function is very simple and easy to use. Let us understand the working of the SEARCH function in some examples.

Example #1

Let us search the “Good” substring in the given text or string. Here, we have found the “Good” word using the SEARCH function, which will return the word “Good” location in the “Good morning.”

=SEARCH(“Good,” B6) and output will be 1.

Suppose two matches are found for “Good,” then SEARCH in Excel will give you the first match value. However, if you want the other good location, then you use the =SEARCH(“Good,” B7, 2) [start_num] as 2 then it will give you the place of the second match value, and the output will be 6.

Example #2

In this example, we will filter out the first and last names from the full names using the SEARCH in excelUsing The SEARCH In ExcelSearch function gives the position of a substring in a given string when we give a parameter of the position to search from. As a result, this formula requires three arguments. The first is the substring, the second is the string itself, and the last is the position to start the search.read more.

For first name=LEFT(B12,SEARCH(” “,B12)-1)

For last name=RIGHT(B12,LEN(B12)-SEARCH(” “,B12))

Example #3

Suppose there is a set of IDs. First, you have to find the _ location within IDs, then use Excel SEARCH to find the “_” location within IDs.

=SEARCH(“_,” B27), and output will be 6.

Example #4

Let us understand the working of SEARCH in excel with wildcards charactersExcel With Wildcards CharactersIn Excel, wildcards are the three special characters asterisk, question mark, and tilde. Asterisk denotes multiple characters, a question mark denotes a single character, and a tilde denotes the identification of a wild card character.read more.

Consider the table and search for the next 0 within the text A1-001-ID.

And start position will be 1, then =SEARCH(“?” &I8, J8, K8) output will be 3 because “?” neglects the one character before the 0, and output will be 3.

For the second row within a given table, the search result for A within B1-001-AY.

It will be 8, but if we use “” in search, it will give you the 1 as location output because it will neglect all characters before “A,” and output will be 1 for it =SEARCH(“” &I9, J9).

Similarly, for “J” 8 =SEARCH(I10,J10,K10) and 7 for  =SEARCH(“?”&I10,J10,K10).

Similarly, for fourth row, the output will be 8 for =SEARCH(I11,J11,K11) and 1 for =SEARCH(“*” &I11,J11,K11)

Things to Remember

  • It is not case-sensitive.It considers tanuj and TANUJ as the same value, which means it does not distinguish between lower and upper case.It is also allowed wildcard characters, i.e., “?”, “,” and “~” tilde.“?” is used to find a single character.“” is used for match sequences.If we want to search the “*” or”? “, we need to insert the “~” before the character.It returns the #VALUE! Error if there is no matching string is found in the within_text.

Suppose in the below example. We are searching for a substring “a” within the “Name” column. If found, it will return the location of a within name else. In addition, it will give a #VALUE error#VALUE Error#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more, as shown below.

  • It considers tanuj and TANUJ as the same value, which means it does not distinguish between lower and upper case.

  • “?” is used to find a single character.

  • “” is used for match sequences.If we want to search the “” or”? “, we need to insert the “~” before the character.

Search Function in Excel Video

This article is a guide to the SEARCH Function in Excel. Here, we discuss the SEARCH formula and how to use the SEARCH function, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Search Box in ExcelSearch Box In ExcelA search box in Excel finds the needed data by typing into it, then filters the data and displays only that much info. When working with large datasheets, this simple tool may save a lot of time.read moreExcel Substring FunctionExcel Substring FunctionThe substring function in Excel is a built-in integrated function that is categorized under the TEXT function and is used to extract a string from a combination of string.read moreFilters in ExcelTrue Function in Excel