Excel SEARCH Function

What is the SEARCH function in Excel?


The SEARCH function is one of the TEXT functions of Excel.

It returns the number of characters at which a specific character or text string is first found, reading left to right (not case-sensitive).

We can find this function in TEXT categoty of insert function Tab.

How to use SEARCH function in excel?

  1. Click on an empty cell (like F5 ).
1

2. Click on fx on the below of font word (or press shift+F3).

3. In insert function tab you will see all functions.

4. Select TEXT category.

5. Select SEARCH function

6. Then select ok.

7. In function arguments Tab you will see SEARCH function.

8. Find text is the text you want to find. You can use the ? and • wildcard characters; use —? and—*to find the ? and * characters.

9. Within text is the text in which you want to search for Find text.

10. Start num is the character number in Within text, counting from the left, at which you want to start searching. If omitted, 1 is used.

11. You will see the results in formula result section.

Examples of SEARCH function in Excel

Example 1: How to find position of a character in Word?

Find the position of “r” character in excellrow.

=SEARCH("r","excellrow",1) ---------->>>>answer is  33

Example 2: How to find position of a word in in a sentence?

Find the position of “excellrow” in “best site of excell is excellrow”.

=SEARCH("excellrow","best site of excell is excellrow",1)                      ---------->>>>answer is  24

Example 3: How to find Position of the first double quotation mark?

Find the position of (“”) in (best “site” of excell is excellrow).

=SEARCH("""",A5,1)---------->>>>answer is  6

Example 4: is the SEARCH function case-sensitive?

The SEARCH function is not case-sensitive. FIND function is case-sensitive.

Example 5: How to find position of a character in the list of Words?

Find the position of “_” character in below Name.

What is the purpose of SEARCH function?

It returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive).

What is the Return value  of SEARCH function?

It just returns the number.

SEARCH (Find text,Within text,Start num)=number

How many arguments does SEARCH function have?

SEARCH (Find text,Within text,Start num)

CODE function has 1 argument.

  • Text is the text for which you want the code of the first character.
  • Within text is the text in which you want to search for Find text.
  • Start num is the character number in Within text, counting from the left, at which you want to start searching. If omitted, 1 is used

Which version of excel supports SEARCH function?

This function is available for all excel versions (2003-2019)

Errors in SEARCH function

If nothing found in text , SEARCH returns #VALUE!

=(SEARCH(A2,B2,1))---------->>>>answer is  #VALUE!
=ISERROR(SEARCH(A2,B2,1))---------->>>>answer is  TRUE
=ISNUMBER(SEARCH(A2,B2,1))---------->>>>answer is  FALSE

Arguments stated as explicit text must be placed within double quotation marks.

Arguments stated as explicit text must be placed within double quotation marks.

=SEARCH(a,"apple",1)---------->>>>answer is  #NAME?

Examples of SEARCH function in Excel

  1. Search for a specific text string within a cell: =SEARCH(“apple”, A1)
  2. Find the position of a text string within a cell: =SEARCH(“dog”, A2)
  3. Use the SEARCH function to look for a string starting from a specified position: =SEARCH(“cat”, A3, 5)
  4. Ignore case when searching for a text string: =SEARCH(“house”, A4)-SEARCH(“house”, LOWER(A4))+1
  5. Use wildcards to search for variations of a text string: =SEARCH(“app*”, A5)
  6. Extract a specific part of a long text string using the SEARCH function: =MID(A6,SEARCH(“:”,A6)+2,SEARCH(“-“,A6)-SEARCH(“:”,A6)-2)
  7. Combine the SEARCH function with other functions to create more complex formulas: =IF(SEARCH(“apple”, A7)>0, “Yes”, “No”)
  8. Search for text in multiple cells or ranges using an array formula: {=MAX(IF(ISNUMBER(SEARCH(“car”,A8:A10)),ROW(A8:A10)))}
  9. Use the SEARCH function to find the first character of a specific text string: =SEARCH(“rat”, A9, 1)
  10. Search for a text string in a case-sensitive manner: =SEARCHB(“Dog”, A10)

What is the syntax for the SEARCH function in Excel?

The SEARCH function in Excel is a string function that allows you to search for a specific text string within another text string. The syntax for the SEARCH function is as follows:

=SEARCH(find_text, within_text, [start_num])

Where:

  • “find_text” is the text string you are searching for
  • “within_text” is the text string within which you want to search for the “find_text”
  • “[start_num]” (optional) is the starting position from where the search should begin. If this parameter is omitted, the search will start from the first character of the “within_text” argument.

Here’s an example to illustrate how the syntax works:

Suppose you have a list of product names in column A and you want to find out if the word “iPhone” appears in any of the product names. You can use the SEARCH function to do this as follows:

=SEARCH(“iPhone”,A1)

In this example, “iPhone” is the “find_text” argument and “A1” is the “within_text” argument. Since we haven’t specified a start position, the search will begin at the first character of the “within_text” argument.

If the SEARCH function finds the “find_text” within the “within_text” argument, it will return the position of the first character of the “find_text”. For example, if “iPhone” appears in cell A1 at position 8, the SEARCH function will return the value 8.

Note that the SEARCH function is not case-sensitive, so “iphone” would also be found in the same way. If you want the search to be case-sensitive, you can use the SEARCHB function instead.

How does the SEARCH function differ from the FIND function?

The SEARCH and FIND functions in Excel are both used to search for a specific text string within another text string, but they differ in their functionality.

The main difference between the two functions is that FIND is case-sensitive, while SEARCH is not. This means that FIND will only find a match if the capitalization of the search term matches the capitalization in the search string, whereas SEARCH will find a match regardless of capitalization.

Here’s an example to illustrate the difference:

Suppose you have the text string “apple pie” in cell A1, and you want to search for the word “pie”. If you use the FIND function, as follows:

=FIND(“Pie”,A1)

Since “Pie” is capitalized differently than it appears in “apple pie,” the FIND function will return an error value (#VALUE!). However, if you use the SEARCH function instead:

=SEARCH(“Pie”,A1)

This will return the position of “pie” in “apple pie.”

Another difference between the two functions is that FIND allows you to specify a third argument for the starting position of the search. If you omit this argument, FIND starts the search at the beginning of the text string. On the other hand, SEARCH allows you to specify a third argument for the starting position of the search, but if you omit this argument, SEARCH starts the search at the first character of the text string.

In general, if you want to perform a case-insensitive search, use the SEARCH function. If you need a case-sensitive search, use the FIND function.

Can the SEARCH function be used to find multiple occurrences of a specific text string in a cell?

Yes, the SEARCH function in Excel can be used to find multiple occurrences of a specific text string within a cell. The key is to use the function as part of an array formula.

An array formula allows you to perform calculations on a range of cells, rather than just a single cell. To use the SEARCH function as part of an array formula, you need to wrap it in the IF function and use the ROWS and MAX functions to handle the results.

Here’s an example:

Suppose you have the following text in cell A1: “The quick brown fox jumps over the lazy dog.” You want to find all instances of the letter “o” in this text. Here’s how you would do it:

  1. Select a range of cells where you want the results to appear, such as B1:B26.
  2. Enter the following formula into the first cell in the range (B1): =IF(ISNUMBER(SEARCH(“o”,$A1,FIND(“∣”,SUBSTITUTE(1,FIND(“∣”,SUBSTITUTE(A1,”o”,”|”,ROW(INDIRECT(“1:”&LEN(A1))))))),ROW(INDIRECT(“1:”&LEN(A$1))),””)
  3. Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula should now appear in curly braces {} in the formula bar.

The formula uses the SUBSTITUTE function to replace each instance of the search term (“o”) with a pipe character (“|”), so that we can count the number of replacements using the ROWS function. We then use the FIND function to locate the position of each occurrence of the search term, and the SEARCH function to determine if the search term appears at that position. If it does, the ROW function returns the row number.

After entering the formula, Excel will return all of the row numbers where the search term appears in the original text string. Note that the result may include blank cells where the search term does not appear in the text string.

Is the SEARCH function case-sensitive?

No, the SEARCH function in Excel is not case-sensitive. This means that it will find a match regardless of the capitalization of the search term or the text string being searched.

For instance, if you use the following formula to search for “apple” in cell A1:

=SEARCH(“apple”,A1)

Excel will return a value indicating the position of the first occurrence of “apple” within the cell. The search will be successful even if the word “Apple” appears with a capital letter or if it is part of a larger string such as “APPLE PIE”.

If you need a case-sensitive search, you can use the SEARCHB function instead. For example, the following formula would perform a case-sensitive search for “apple” in cell A1:

=SEARCHB(“apple”,A1)

In this case, the search would only be successful if “apple” appears exactly as it is written, including capitalization. If the word “Apple” appears in the text string, the SEARCHB function would return an error value (#VALUE!).

What happens if the SEARCH function does not find the specified text string?

If the SEARCH function in Excel is unable to find the specified text string within the cell being searched, it will return a #VALUE! error value.

For instance, if you use the following formula to search for “apple” in cell A1:

=SEARCH(“apple”,A1)

Excel will return a value indicating the position of the first occurrence of “apple” within the cell. However, if “apple” is not found within the cell, Excel will return a #VALUE! error.

To prevent this error from appearing, you can use the ISNUMBER function to check whether the SEARCH function was successful or not. The ISNUMBER function returns TRUE if the SEARCH function returns a number, indicating that the text string was found, and FALSE otherwise.

Here’s an example:

Suppose you have a list of product names in column A, and you want to create a new column that indicates whether each product name contains the word “apple”. You could use the following formula in cell B1:

=IF(ISNUMBER(SEARCH(“apple”,A1)),”Contains apple”,”Does not contain apple”)

This formula uses the SEARCH function to look for the word “apple” in the product name in cell A1. If the word is found, the SEARCH function will return a number, which causes the ISNUMBER function to return TRUE. The IF function then returns the text “Contains apple” as the result. If the word “apple” is not found, the SEARCH function will return an error value, causing the ISNUMBER function to return FALSE. The IF function then returns the text “Does not contain apple” as the result.

Can wildcards be used with the SEARCH function?

Yes, wildcards can be used with the SEARCH function in Excel. The SEARCH function is used to find a specific character or string of characters within another text string. It returns the starting position of the character or string that you are searching for within the text string.

Wildcards are special characters that represent one or more characters. They are often used when you want to search for a pattern rather than a specific character or string. In the context of the SEARCH function, you can use two types of wildcards:

  1. Asterisk (*): This represents any number of characters. For example, if you want to find all instances of the word “apple” within a larger text string, you could use the following formula:

=SEARCH(“apple”, A1)

However, if you wanted to find all instances of words that start with “app” and end with “e”, regardless of the number of characters in between, you could use the following formula:

=SEARCH(“app*e”, A1)

The asterisk (*) represents any number of characters between “app” and “e”.

  1. Question mark (?): This represents a single character. For example, if you want to find all instances of the word “color” within a larger text string, but you’re not sure whether it’s spelled “color” or “colour”, you could use the following formula:

=SEARCH(“col?r”, A1)

The question mark (?) represents a single character, so this formula would find both “color” and “colour”.

In summary, wildcards can be very useful when using the SEARCH function in Excel. They allow you to search for patterns rather than exact matches, which can save time and effort when working with large amounts of text data.

How can I use the SEARCH function to extract a specific part of a longer text string?

The SEARCH function in Excel can be very useful for extracting specific parts of longer text strings. Here’s how you can use it:

Let’s say you have a list of email addresses in column A, and you want to extract just the domain name (i.e. the part of the email address after the “@” symbol) into column B.

  1. Start by entering the formula “=SEARCH(“@”,A1)” in cell B1. This will return the position of the “@” symbol within the text string in cell A1.
  2. To extract the domain name, you’ll need to know the length of the domain name. You can do this by subtracting the position of the “@” symbol from the total length of the text string. Enter the formula “=LEN(A1)-B1” in cell C1 to calculate the length of the domain name.
  3. Finally, you can use the LEFT function to extract the domain name from the original text string. Enter the formula “=LEFT(A1,C1)” in cell B1 to extract the domain name.

Here’s an example of what your worksheet might look like:

ABC
john@doe.comdoe.com7
jane@smith.netsmith.net9
bob@example.comexample.com12

Note that the SEARCH function is used to find the position of the “@” symbol in each cell in column A. The length of the domain name is then calculated using the LEN function and subtracting the position of the “@” symbol. Finally, the LEFT function is used to extract the domain name based on the calculated length.

In summary, the SEARCH function can be used along with other functions like LEN and LEFT to extract specific parts of longer text strings in Excel. With a little bit of practice, you can use this technique to extract all kinds of useful information from text data.

Can the SEARCH function be combined with other functions in Excel formulas?

Yes, the SEARCH function in Excel can be combined with other functions to create more powerful and flexible formulas. Here are some examples:

  1. Using SEARCH with LEFT or RIGHT: You can use the SEARCH function along with the LEFT or RIGHT function to extract a specific number of characters from a text string that contains a certain pattern. For example, if you have a list of product codes in column A and you want to extract the first four characters of each code that start with “PRD_”, you could use the following formula in cell B1:

=LEFT(A1,SEARCH("PRD_",A1)+3)

This formula uses the SEARCH function to find the position of the “PRD_” pattern in cell A1, and then adds 3 to include the three characters after the pattern. The LEFT function is then used to extract the specified number of characters from the left side of the text string.

  1. Using SEARCH with IF: You can use the SEARCH function along with the IF function to test for the presence of a certain pattern in a text string and return a specific value if that pattern is found. For example, if you have a list of job titles in column A and you want to classify each title as either “Manager” or “Non-Manager” based on whether it contains the word “manager”, you could use the following formula in cell B1:

=IF(SEARCH("manager",A1),"Manager","Non-Manager")

This formula uses the SEARCH function to find the position of the word “manager” within the text string in cell A1. If the word is found (i.e. the SEARCH function returns a non-zero value), the formula returns the value “Manager”. If the word is not found (i.e. the SEARCH function returns zero), the formula returns the value “Non-Manager”.

  1. Using SEARCH with SUBSTITUTE: You can use the SEARCH function along with the SUBSTITUTE function to replace a specific pattern within a text string with another value. For example, if you have a list of phone numbers in column A that are formatted as “(555) 123-4567” and you want to remove the parentheses and hyphen to create a numeric-only phone number, you could use the following formula in cell B1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")

This formula uses the SUBSTITUTE function to replace the “(” symbol, “)” symbol, and “-” symbol with an empty string (“”). The SEARCH function is not used directly in this formula, but it is used implicitly by the SUBSTITUTE function to find the positions of the specified patterns.

In summary, the SEARCH function can be combined with other functions in Excel formulas to extract, classify, or transform text data based on certain patterns. By using these combinations creatively, you can gain insights from your text data and automate many tedious tasks.

How can I use the SEARCH function to search for text in multiple cells or ranges?

The SEARCH function in Excel can be used to search for text in multiple cells or ranges by using arrays. Here’s how you can do it:

Let’s say you have a list of product descriptions in column A, and you want to find all instances where the word “red” appears in the description. You also have a list of category names in column B, and you want to highlight the categories that contain the word “clothing”. Here’s what you can do:

  1. To search for the word “red” in column A, enter the following formula in cell C1: =IF(SUM(--(SEARCH("red",A1:A10)>0))>0,"Yes","No") This formula uses the SEARCH function to look for the word “red” within the range A1:A10. The double negative (–), along with the SUM function, converts the resulting array of TRUE/FALSE values into an array of 1s and 0s, where 1 represents a match and 0 represents no match. The IF function is then used to return “Yes” if any matches are found, and “No” if not.
  2. To highlight the categories that contain the word “clothing” in column B, select the range of cells B1:B10 (or whatever range you need to search), and then click on “Conditional Formatting” in the “Home” tab of the ribbon. Choose “New Rule”, then “Use a formula to determine which cells to format”.
  3. In the formula box, enter the following formula: =SUM(--(ISNUMBER(SEARCH("clothing",B1:B10)))) This formula uses the SEARCH function along with the ISNUMBER function to check whether the word “clothing” appears anywhere within the range B1:B10. The result is an array of TRUE/FALSE values, which are then converted to an array of 1s and 0s using the double negative (–). The SUM function is then used to add up the values in the array, so if any matches are found, the sum will be greater than zero.
  4. Choose a formatting style (e.g. fill color) for cells that match the criteria.

Here’s an example of what your worksheet might look like:

ABC
Red shirtClothingYes
Blue pantsElectronicsNo
Green dressJewelryNo
Red hatClothingYes
Black shoesClothingYes
White T-shirtClothingYes
Women’s clothing bundleWomen’s ClothingYes
Men’s clothing clearanceMen’s ClothingNo
Women’s shoesShoesNo
Men’s socksSocksNo

Note that the SEARCH function is used with an array in both cases: in cell C1 to search for the word “red” within the range A1:A10, and in the conditional formatting rule to search for the word “clothing” within the range B1:B10.

In summary, you can use the SEARCH function in combination with arrays to search for text in multiple cells or ranges in Excel. This technique can help you quickly find patterns or trends within large sets of text data, and automate many tedious tasks involving text analysis.

Are there any limitations or performance considerations when using the SEARCH function in large datasets?

Yes, there are some limitations and performance considerations that you should be aware of when using the SEARCH function in large datasets.

  1. Case sensitivity: The SEARCH function is case-insensitive by default, which means that it will find matches regardless of whether the text is uppercase or lowercase. However, if your dataset contains a mix of uppercase and lowercase text and you need to make a case-sensitive search, you’ll need to use the FIND function instead of SEARCH.
  2. Performance: The SEARCH function can be slow when used with very large datasets, especially if you’re searching for complex patterns or using multiple instances of the function in a single formula. In these cases, it’s often better to use other functions like FILTER, SORT, or SUMIF, which can perform faster on large datasets.
  3. Limitations of wildcards: While wildcards can be very useful for searching for patterns within text data, there are some limitations to their use. For example, if you’re using an asterisk (*) to represent any number of characters, you may accidentally match more text than you intended. Similarly, if you’re using a question mark (?) to represent a single character, you may miss matches where the text contains more or fewer characters than you expect.
  4. Data types: The SEARCH function is designed to work with text strings, so if your dataset includes numbers or dates, you’ll need to convert them to text format before using the function. This can be done using the TEXT function, which allows you to specify a custom format for converting numbers or dates to text.
  5. Range limits: Excel has a limit of 1,048,576 rows and 16,384 columns per worksheet. If you’re working with larger datasets than this, you’ll need to split them into multiple worksheets or use a database management system like Access or SQL Server.

In summary, while the SEARCH function can be very useful for searching for patterns within text data, there are some limitations and performance considerations to keep in mind when using it with large datasets. By being aware of these factors and using other functions when necessary, you can ensure that your Excel worksheets perform optimally and provide reliable results.

Leave a Reply

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